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.

[ruma_barman]

Hi Tresesco,

Many thanks for your help.

One more thing-How I have to write the set analysis for the same if I have to divide it with previous month.

Like for Mar17: (revenue of Mar'17-revenue of Feb'17)/revenue of Feb'17

[tresesco]

Like this:

Aggr(

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

/above((Sum({<[Month Name]>}Revenue)))

,[Month Name]

)

[Anil_Babu_Samineni]

Like this?

Aggr(

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

,[Month Name])

/above((Sum({<[Month Name]>}Revenue)))

,[Month Name]

)

Or

(Sum({<[Month Name] = {'$(=MonthName(Max([Month Name])))'}>}Revenue) - Sum({<[Month Name] = {'$(=MonthName(Max([Month Name])-1))'}>}Revenue))/

Sum({<[Month Name] = {'$(=MonthName(Max([Month Name])-1))'}>}Revenue)

[ruma_barman]

Hi Tresesco,

When I try to apply the expression in my actual application(in qliksense),the above function is not taking the previous month name as expected.Eg: For Mar 2017,it should take previous month i.e Feb 2017 and calculate but it is taking Apr 2015 .The expression is calculated in the following sequence which I am not able to get how.

Can you please help on this?

Thanks,

Ruma

[tresesco]

That would probably be because - your date field is being treated as string rather than proper date. Try to create your date in the script, using date functions like:

MonthName(Date#("Month Name",'MMM YYYY')) as "Month Name"

[ruma_barman]

Tried.Not working.

[joydipp1988]

Following Tresesco's expression I've made a solution. PFA. Find the solution at Sheet1. To reload the document, put data file and qvw file in same folder.

Thanks,

Joy !

[Peter_Cammaert]

Maybe because you presented your months in reverse order?

Change Above() into Below().

[ruma_barman]

Still the same.Can this expression be written using before function coz the sequence comes proper that way?

[ruma_barman]

Hi Tresesco,

Can any expression be written using before function which serves the same purpose?

Regards,

Ruma