Hi all,
I am using the cross tab and i need to add the particular columns in that
ex: nov2015 dec2015 Jan2016 Feb2016 variance (jan2016-feb2016)
India 200 300 400 300 100
Us 150 200 100 250 -150
here i am using lastfourmonths member in crosstab column.
how will i get the variance for above crosstab.
Thanks in advance,
Karthee
You need to isolate the last two members within your last four months set and then subtract. This example is done using the year level of the time dimension in the GO Sales (analysis) package.
aggregate ( currentMeasure within set head ( tail ( [Sales (analysis)].[Time].[Time].[Year], 2 ) ) )
-
aggregate ( currentMeasure within set tail ( [Sales (analysis)].[Time].[Time].[Year], 1 ) )
Using the tail function for 2 items and then getting the first will give you Jan 2016. Using the tail function for 1 item will get you Feb 2016. Define each of these as the set for your aggregate function and subtract.
There might be a more elegant expression to achieve this in which case hopefully someone else will chime in :)