topic Re: Set Expression in Pivot Table in QlikView

Set Expression in Pivot Table

upaliwije — Wed, 29 May 2019 11:17:02 GMT

Hi Friends

I have shown the following screen shot of my pivot table

My expression for the above is

(sum({<MONTH={"<=$(=max(MONTH))"},YEAR={$(=max(YEAR))},MO_NM={'MOTOR'}>}PREMIUM)/
sum({<MONTH={"<=$(=max(MONTH))"},YEAR={$(=max(YEAR)-1)},MO_NM={'MOTOR'}>}PREMIUM)-1)*100

What I want is when I select a particular MONTH all months less than the selected month should show calculation as above and rest of the months eg: May-Dec should show zero value. Kindly help me to modify my expression to show zero values for other months.

Re: Set Expression in Pivot Table

upaliwije — Thu, 30 May 2019 03:18:50 GMT

My Friends,

Pls help me with asolution

Re: Set Expression in Pivot Table

rwunderlich — Thu, 30 May 2019 04:18:49 GMT

You just need to wrap your current expression with an if() like this:

if(MONTH > Max(TOTAL MONTH), 0,
your current expression
)

-Rob

Re: Set Expression in Pivot Table

upaliwije — Thu, 30 May 2019 04:52:51 GMT

Thanks

I have put it like this

if(MONTH > Max(TOTAL MONTH), 0,
(sum({<MONTH={"<=$(=max(MONTH))"},YEAR={$(=max(YEAR))},MO_NM={'MOTOR'}>}PREMIUM)/
sum({<MONTH={"<=$(=max(MONTH))"},YEAR={$(=max(YEAR)-1)},MO_NM={'MOTOR'}>}PREMIUM)-1)*100)

Still the output is the same , No months appear after max month

Re: Set Expression in Pivot Table

Brett_Bleess — Tue, 06 Aug 2019 14:47:38 GMT

About the best I can do in addition to Rob's comment is some links that might be of some help:

Help site:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/ChartFunctions/SetAnalysis/set-analysis-expressions.htm

Design Blog:

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

There are additional posts in the Design Blog area too, just FYI. Hopefully this may help you get things sorted.

Regards,
Brett