Set Analysis in Pivot Table

[ruma_barman]

There is a dimension named Revenue and a dimension named Month Name from master calender and I have to show

in a pivot table Month Name as the dimension and expression is sum of revenue for the corresponding month minus sum of revenue of the previous month.

Example: For Jan 2017 ,it should show sum of revenue of Jan 2017 minus sum of revenue of Dec 2016 in the table.

Refer screenshot.

[Anil_Babu_Samineni]

May be this?

Sum({<[Month Name] = {'$(=Max([Month Name]))'}>}revenue)-Sum({<[Month Name] = {'$(=AddMonths(Max([Month Name]),-1))'}>}revenue)

[ruma_barman]

Hi Anil,

Thanks for you reply,but it didn't work that way.

Thanks,

Ruma

[Anil_Babu_Samineni]

Why, May be some other work would be needed. Can you provide Sample

[tresesco]

Try like:

RangeSum( Sum(Revenues), - Before(Sum(Revenues)))

[ruma_barman]

Hi Tresesco,

In this case when two months are selected ,say Dec'16 and Mar'17,Mar'17 will show revenue of Mar'17 - revenue of Dec'16 but actually it should show revenue of Mar'17 - revenue of Feb'17.

[tresesco]

Then, you might have to use set analysis in combination with aggr(). Try to share your sample qvw for specific help.

[ruma_barman]

Hi Anil,

One sample data is attached.

[ruma_barman]

Hi Tresesco,

One sample data attached.

[tresesco]

Try expression like:

Aggr(

          Rangesum(Sum({<[Month Name]>}Revenue),-above((Sum({<[Month Name]>}Revenue))))

,[Month Name])