topic Re: Set Analysis in Pivot Table in QlikView

Set Analysis in Pivot Table

ruma_barman — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Set Analysis in Pivot Table

Anil_Babu_Samineni — Fri, 05 May 2017 09:34:24 GMT

May be this?

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

Re: Set Analysis in Pivot Table

ruma_barman — Fri, 05 May 2017 10:17:03 GMT

Hi Anil,

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

Thanks,

Ruma

Re: Set Analysis in Pivot Table

Anil_Babu_Samineni — Fri, 05 May 2017 10:18:51 GMT

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

Re: Set Analysis in Pivot Table

tresB — Fri, 05 May 2017 10:28:38 GMT

Try like:

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

Re: Set Analysis in Pivot Table

ruma_barman — Fri, 05 May 2017 10:46:34 GMT

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.

Re: Set Analysis in Pivot Table

tresB — Fri, 05 May 2017 10:50:39 GMT

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

Re: Set Analysis in Pivot Table

ruma_barman — Fri, 05 May 2017 10:56:13 GMT

Hi Anil,

One sample data is attached.

Re: Set Analysis in Pivot Table

ruma_barman — Fri, 05 May 2017 11:14:22 GMT

Hi Tresesco,

One sample data attached.

Re: Set Analysis in Pivot Table

tresB — Fri, 05 May 2017 11:16:12 GMT

Try expression like:

Aggr(

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

,[Month Name])

Re: Set Analysis in Pivot Table

ruma_barman — Fri, 05 May 2017 12:31:30 GMT

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

Re: Set Analysis in Pivot Table

tresB — Fri, 05 May 2017 12:40:54 GMT

Like this:

Aggr(

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

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

,[Month Name]

)

Re: Set Analysis in Pivot Table

Anil_Babu_Samineni — Fri, 05 May 2017 12:43:29 GMT

Like this?

Aggr(

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

,[Month Name])

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

,[Month Name]

)

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

Re: Set Analysis in Pivot Table

ruma_barman — Mon, 08 May 2017 12:35:55 GMT

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

Re: Set Analysis in Pivot Table

tresB — Mon, 08 May 2017 12:41:20 GMT

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"

Re: Set Analysis in Pivot Table

ruma_barman — Mon, 08 May 2017 13:50:28 GMT

Tried.Not working.

Re: Set Analysis in Pivot Table

joydipp1988 — Mon, 08 May 2017 15:42:26 GMT

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 !

Re: Set Analysis in Pivot Table

Peter_Cammaert — Mon, 08 May 2017 16:07:24 GMT

Maybe because you presented your months in reverse order?

Change Above() into Below().

Re: Set Analysis in Pivot Table

ruma_barman — Tue, 09 May 2017 07:00:15 GMT

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

Re: Set Analysis in Pivot Table

ruma_barman — Tue, 09 May 2017 07:56:49 GMT

Hi Tresesco,

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

Regards,

Ruma