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 ?
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".
Quote from: oscarca on 21 Nov 2018 01:03:43 PM
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.
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
)
This is example image of how it looks like
https://imgur.com/a/l1C0qvd (https://imgur.com/a/l1C0qvd)
In the far end of the crosstab columns you can see a blank column.
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
Quote from: oscarca on 27 Nov 2018 07:50:32 AM
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.
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