Rolling R12 data item relational crosstab

[oscarca]

Hey Cognos,

I am trying to accomplish a rolling R12 expression that sums up each months R12. For example lets say you choose to view 2018/Dec - 2018/Jan then number in the 2018/Dec month should be 2018/Jan + 2018/Feb + 2018/Mar ........... + 2018/Dec and the number in 2018/Nov should be 2017/Dec + 2018/Jan etc etc

I have done this against a cube using lag function but never when working with a relational data source. I assume I need to create 12 data items.

[Selected month ] query looks like this:
if(substring(cast([Business].[Date].[Date];varchar(10));1;7) between substring(cast(_add_months(cast(?pAck?+'-01';date );-11);VARCHAR(10));1;7) and ?pAck?)
then ([Month])
else(
null
)

then Month lag 1 data item looks like this:
if(substring(cast(cast([Business].[Date].[Date];date);varchar(10));1;7) between substring(cast(_add_months(cast(?pAck?+'-01';date );-12);VARCHAR(10));1;7) and substring(cast(_add_months(cast(?pAck?+'-01';date );-1);VARCHAR(10));1;7))
then([Month)
else(
null
)

and so on but not getting it quite to work....
Anyone here done similar expressions with relational data and crosstab ?

[oscarca]

For some reason the "Else-statement" creates a second column with every value that doesnt belong to the the date filter. So I wonder what couldnt be written instead of "else null".

[MFGF]

Hey Cognos,

I am trying to accomplish a rolling R12 expression that sums up each months R12. For example lets say you choose to view 2018/Dec - 2018/Jan then number in the 2018/Dec month should be 2018/Jan + 2018/Feb + 2018/Mar ........... + 2018/Dec and the number in 2018/Nov should be 2017/Dec + 2018/Jan etc etc

I have done this against a cube using lag function but never when working with a relational data source. I assume I need to create 12 data items.

[Selected month ] query looks like this:
if(substring(cast([Business].[Date].[Date];varchar(10));1;7) between substring(cast(_add_months(cast(?pAck?+'-01';date );-11);VARCHAR(10));1;7) and ?pAck?)
then ([Month])
else(
null
)

then Month lag 1 data item looks like this:
if(substring(cast(cast([Business].[Date].[Date];date);varchar(10));1;7) between substring(cast(_add_months(cast(?pAck?+'-01';date );-12);VARCHAR(10));1;7) and substring(cast(_add_months(cast(?pAck?+'-01';date );-1);VARCHAR(10));1;7))
then([Month)
else(
null
)

and so on but not getting it quite to work....
Anyone here done similar expressions with relational data and crosstab ?

Hi,

Shouldn't your 'Then' clause be the measure value you want to include, ie

if (blah blah blah)
then ([Your measure value])
else (null)

Or am I misunderstanding?

MF.

[oscarca]

the "Then" clause includes the months that match the requirement of the data item filter expression. So if the months are between the expression for example current date and - 11 months then "month data item" ie 2018/ nov - 2017/dec for example.

so the expression below gives me the correct months but also gives me a blank column at the end giving me a total value of everything that doesnt belong to that time period. 

if(substring(cast(cast([Business].[Date].[Date];date);varchar(10));1;7) between substring(cast(_add_months(cast(?pAck?+'-01';date );-12);VARCHAR(10));1;7) and substring(cast(_add_months(cast(?pAck?+'-01';date );-1);VARCHAR(10));1;7))
then([Month)
else(
null
)

[oscarca]

This is example image of how it looks like
https://imgur.com/a/l1C0qvd

In the far end of the crosstab columns you can see a blank column.

[oscarca]

I am willing to try another method if someone know another way to do this.

All I want is to do a dynamic rolling total for R12 where I can see each months total r12

                2016/jan (2015/feb + 2015/mars + 2015/apr etc up to jan )   2016/Feb (2015/mars + 2015/mars + 2015/apr etc up to feb )     

Amount of orders

[MFGF]

I am willing to try another method if someone know another way to do this.

All I want is to do a dynamic rolling total for R12 where I can see each months total r12

                2016/jan (2015/feb + 2015/mars + 2015/apr etc up to jan )   2016/Feb (2015/mars + 2015/mars + 2015/apr etc up to feb )     

Amount of orders

Hi,

I'm confused. You are saying in this latest post that you want to see a rolling [bold]total[/bold], so wouldn't this be a measure value summarised for the relevant months? Why are you trying to return the month values in your expression, when your expression already identifies these in the 'if'?

Wouldn't your expression be

if(substring(cast(cast([Business].[Date].[Date];date);varchar(10));1;7) between substring(cast(_add_months(cast(?pAck?+'-01';date );-12);VARCHAR(10));1;7) and substring(cast(_add_months(cast(?pAck?+'-01';date );-1);VARCHAR(10));1;7))
then([Your measure value])
else(
null
)

Can you explain in detail what you are trying to do?

MF.

[oscarca]

I want to create a dynamic R12 for each month displayed in the crosstab:
[Lag12]        [Lag11]        [Lag10]       [Lag9]          [Lag8]        [Lag7]        [Lag6]      [Lag5]     [Lag4]      [Lag3]            [Lag2]
2018/Jan      2018/feb      2018/mar     2018/apr     2018/May    2018/jun    2018/jul   2018/aug   2018/sep   2018/okt    2018/Nov  < -  trailing 12 month [Selected Month (lets say 2018/dec)]


The sum for 2018/dec = jan + feb + mar + apr + may+ jun + jul + aug + sep + okt + nov + dec
The sum for 2018/nov = 2017/dec + jan + feb + mar + apr+ may + jun + jul+ aug + sep + okt + nov
The sum for 2018/okt =2017/nov + 2017/dec + jan + feb + mar + apr+ may + jun + jul+ aug + sep + okt

thats why I created 12 data items to place in the columns.

lets say that we have these orders:
                              2018/Jan      2018/feb      2018/mar     2018/apr     2018/May    2018/jun    2018/jul   2018/aug   2018/sep   2018/okt    2018/Nov   2018/dec
Amount of order           1                  1                  1                 1                  1               1                 1           1                  1            1               1                 1

which gives me a total of 12 orders and that would be the number I wish to display for the data item "Selected month" which was 2018/dec in the example above with the lags.

                          2017/dec      2018/jan      2018/feb     2018/mar     2018/april    2018/may    2018/jun   2018/jul   2018/aug   2018/sep    2018/okt       2018/nov
Amount of order           4                  1                  1                 1                  1               1                 1           1                  1            1               1                 1

which gives me a total of 15 orders and that would be the number I wish to display for the data item "Lag2" which in these case is 2018/nov

I hope this makes sense.
So basically a total rolling 12 that is based on a prompt selection.

The 12-month rolling sum is simply the total amount from the past 12 months. As the 12-month period "rolls" forward each month, the amount from the latest month is added and the one-year-old amount is subtracted. The result is a 12-month total that has rolled forward to the new month

So in this case for [Lag2] (2018/nov) , 2018/dec is subtracted and 2017/dec is added. For 2018/okt, 2018/nov is subtracted and 2017/nov is added etc.
Thank you for helping me MF