Changing the dimension value in pivot table according to the selection made in list box

[Anonymous]

hi.

Consider pivot table with two dimension and a expression

dimensions - product and quarter/Year of 4 Years 2010,2011,2012,2013

expression - sum of product

Example:

product          Quarter/Year       Sum

     a                   q1/2010         100

                          q2/2010         200

                          q3/2010         130

                          q1/2011         100

                          q2/2011         100

                          q3/2011         130

     b                  q1/2010         150

                          q2/2010         230

                          q3/2010         130

Consider a list box with all quarter/Year for 4 years respectively 2010,2011,2012,2013.

Here, i dont want to display the pivot table with all 4 years with their quarters. I want to display only those years with quarters which i select in listbox. If i didn't select any from listbox, it should display only the latest year with their quarters and their sum. Help me finding this.

Thanks.

[Anonymous]

I think you are looking for this kind of functionality, check this?

Adhoc reporting in Qlikview

Ad-Hoc Reporting in Qlikview

[avinashelite · Accepted solution]

Try like this :

1.Add a calculated dimension like

// to get the last year data

if(getselectedcount(quarter/Year)=0 and subfield(quarter/Year,'/',2)=year(Today())-1,quarter/Year,

if(getselectedcount(quarter/Year)>0,quarter/Year

)

)

[Anonymous]

This is common association feature in QV, whatever selection you will do in QV it will automatically filter and  will show that data only!!

Are you facing any issue?

[Anonymous]

The selection which i make in list box has to reflect in the dimension value in the pivot table. This is what i want and m facing prob in that.

[Anonymous]

Thanks. it helped me. This is what i was looking for.

But i need to get 2016 (latest year)  and its Quarter 'Quarter/Year' while first loading my app, and then i need to change respectively. Can you  help me in that

[avinashelite]

You can add a trigger for the same

Go to Document properties >triggers > on open > select the field quarter/Year and in the value bar add this expression

=if(subfield(quarter/Year,'/',2)=year(Today()),quarter/Year)

or you can change the expression like this

if(getselectedcount(quarter/Year)=0 and subfield(quarter/Year,'/',2)=year(Today()),quarter/Year,

if(getselectedcount(quarter/Year)>0,quarter/Year

)

)

Hope this helps you

[Anonymous]

i tried as yu said. but still i cant able to see the change while i m loading.

i cant see the Quarter/Year got selected in the pivot table.!

and i tried with that expression too. its not working.

[avinashelite]

Do you have the data for the current year?? i.e. for 2016

[Anonymous]

yes..i do have data for 2016. what may be missing.?