topic Re: Need some help with a Pivot Table and excluding some values in QlikView

Need some help with a Pivot Table and excluding some values

davidstrong — Mon, 05 Mar 2018 18:21:39 GMT

Hi all,

I have a Pivot Table Chart that has a single dimension that shows a Month year combination. We have loaded some future dated data to test some enhancements, but want to prevent any date that is in the future from being listed in this Pivot Table drop down menu. I have attached a screenshot to show what I want to eliminate. Is this possible to do in from the properties of the object?

Re: Need some help with a Pivot Table and excluding some values

devyanshu_gupta — Tue, 06 Mar 2018 14:26:34 GMT

You can use a calculated dimension, for instance

if(Date<=today(),"MonthYear")

And suppress null values.

Re: Need some help with a Pivot Table and excluding some values

jaibau1993 — Tue, 06 Mar 2018 15:53:09 GMT

Hi David!

What about using a calculated dimension instead of an unmodified one? In the dimension sheet of the chart you can "add calculated dimension" so you can write something of the form

=if(MonthYear <= CurrentMonthYear, MonthYear)

where CurrentMonthYear is the combination of month-year of the current day. You can automatically store this info in a variable when the document is opened (Document properties/triggers or sheet properties/triggers) and use that variable in the above "if" statement somehow.

Hope it works! Let me know if you have trubles with the "if" condition!

Re: Need some help with a Pivot Table and excluding some values

davidstrong — Wed, 07 Mar 2018 15:57:53 GMT

That did what I needed. I could not get the syntax down, so thanks for your help.

Re: Need some help with a Pivot Table and excluding some values

isingh30 — Wed, 07 Mar 2018 16:03:31 GMT

Can you share your data or app?

Thank you!