Time comparisons

[thabrane]

Hello,
I have a report which compares current month, year to date, and prior year to date. I have to be able to run this report for various months. For instance, I want to be able to filter this report for june, and then filter the same report for march, while keeping the YTD and prior YTD for each month chosen.

Is there any way to create a cube so that I can perform this analysis?
Any help would be appreciated.

Thanks

 

[flex13]

You could look into creating relative time categories inside of the Transformer model. On possibly for each combination that your require, or a set of categories based off of the current period (2 months ago, 6 months ago, etc).

 

[thabrane]

I've thought about doing that.....but I would like to limit the amount of time dimensions that I have.
One possibilty I am looking into right now is using measures. I created a value in my iqd called current month which uses sales $ as a value. This works fine - I filter on a specific month and it only returns that month.

I then created a field called YTD which also uses Sales $. I am thinking that if I can use a running total for this value, I may be able to get it to return the correct amount when I filter on a specific month. So far, I have been unsuccessful with this method, but am still working on it.

Thanks

 

[thabrane]

I have tried using a calculation in the iqd to create a measure for YTD Sales, but I can't seem to get it working properly. I am not sure what I have to associate it for. This is really driving me crazy. I have tried various combinations of associations and none seem to work.

Any suggestions would be greatly appreciated.

Thanks.

 

[DoubleD]

If you could give us a visual of the original data, then what you want the data to look like, it would help us help you.

Pain is stress leaving the body.

DoubleD

 

[thabrane]

I am comparing business information by business unit within my company. I need to show the business units as rows, and in the columns I need to show current month sales, current month margin, YTD sales, YTD margin. I want to be able to filter on a month and show current and YTD values for that particular month.
For instance, if I am lookin at may on the report, it should show may (current month) and Jan + Feb + March + April + May (YTD). When I filter on a different month (Feb), I would like to see Feb (current month) and Jan + Feb (YTD Feb).
Any idea how I could get this done?
Thanks

 

[DoubleD]

If you're going against Oracle, your calculated columns can be based on decode statements.

[i]Current Month Sales[/i]:Total(Decode(FirstOfMonth([i]Sale Date[/i]), FirstOfMonth($prompt$), [i]Sales Dollars[/i],0))

[i]Current Month Cost[/i]:Total(Decode(FirstOfMonth([i]Sale Date[/i]), FirstOfMonth($prompt$), [i]Cost Dollars[/i],0))

[i]Current Month Margin[/i]: ([i]Current Month Sales[/i] - [i]Current Month Cost[/i]) / [i]Current Month Sales[/i]

[i]YTD Sales[/i]: Total(Decode(Year($prompt$),Year(Now()),Decode(Sign(Month(Now())-Month($prompt$),1,0,[i]Sales Dollars[/i]),0))

etc..

The YTD calculation looks at the Year, then looks at the month, and if the today's Month is less than or equal to the prompts month, it returns the Sales Dollars.

Pain is stress leaving the body.

DoubleD

 

[thabrane]

Thanks for your effort......unfortunately, we are using SQL Server databases.
The way that I was thinking of doing it was to create 2 calculated columns in my iqd. The first one, to be used for the current month, would just be based on the sales $. This would filter for the correct months' worth of data when a filter is chosen in the powerplay report.
The second column would represent the YTD value. I thought that I could do an accumulation of the Sales $ and have it associated correctly so that when I filter on a particular month in PowerPlay, it returns the correct YTD value. I tried associating it to the month (extracted from the time field using the 'month' function), the year (also extracted from the time field using the year function), I even tried just using the time field itself. I just can't seem to get it to return the correct value.

Any suggestions on how I can get this to work would be a great help.

Thanks

 

[DoubleD]

If you're using SQL Server, just replace the Decode statements with If Then Else statements and the calculations should work fine.

Pain is stress leaving the body.

DoubleD