adding measures in crosstab

[karthik12345]

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
         

[Lynn]

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.

Code Select Expand


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  :)