topic 1 Filter for 2 Pivot tables - Set Analysis in App Development

1 Filter for 2 Pivot tables - Set Analysis

Jaquin — Fri, 08 Jul 2022 11:36:12 GMT

Hello I got a Problem i cant find a solution for. I hope you can help me.

I have the following season.display_name fiel in a table:
07-FS
07-HW
08-FS
08-HW
09-FS
09-HW

and when i choose for example 09-HW in the Filter i want pivot table 1 to show me the data of that season and pivot table 2 to show me the data of 2 seasonse before (08-HW).

I cant find a solution within my set analysis ...

There is also an ID field that could be used for that.

Can someone probably help me out?

If you need me to provide any further Information i will try to do so. Thank you very much in advance for your effort.

Re: 1 Filter for 2 Pivot tables - Set Analysis

Jaquin — Fri, 08 Jul 2022 11:51:57 GMT

I tried it with the season.id field but it did not work out:
Sum(
{$<season_uid = {"=$(=(fieldvalue(season_uid,-2)"}
>}
sales_total)

Any Advice on how i could use set analysis in the 2nd pivot table to get the correct sum?

Re: 1 Filter for 2 Pivot tables - Set Analysis

MartW — Fri, 08 Jul 2022 13:30:52 GMT

is it an idea to start working with states? create a state for pivot 1 and the filter and a state for pivot 2 and a filter. that way you can independently from each other use a filter to show the data

Re: 1 Filter for 2 Pivot tables - Set Analysis

Gui_Approbato — Fri, 08 Jul 2022 15:02:09 GMT

Hello,

I created a table assigning values to the seasons, then I use this order in the set analysis to bring the desired result.

Using the {1} modifier we ignore the selection, but we use it to bring the results from 2 seasons before our "sorting" table

Only({1<Season_Order={'$(=Season_Order-2)'}>}Season)

I made some sample data and you can find the logic in the qvf attached.

Re: 1 Filter for 2 Pivot tables - Set Analysis

Jaquin — Sat, 09 Jul 2022 11:41:49 GMT

Hello MartW,

I can not use alternative States, since i have some filters which apply to both tables and only the season shall be different.

Or can i apply more filters for both Tables and only 1 different for each other?

Re: 1 Filter for 2 Pivot tables - Set Analysis

Jaquin — Mon, 11 Jul 2022 11:54:30 GMT

Thank you very much for the effort.

I am not sure if it works properly.

When i choose for example 21-HW it shows for the current selection 895.000 and the table 2 seasons before (20-hw) shows 0.
When i then choose 22-HW it shoule give me those 895000 for the table 2 seasons before right? But then it shows 70.313.000

Furthermore the other filters do not influence the column anymore. But i need them to influence it.

Re: 1 Filter for 2 Pivot tables - Set Analysis

Gui_Approbato — Tue, 12 Jul 2022 12:07:41 GMT

Hello Jaquin,

I made this short video to show you the logic:

https://youtu.be/YqLlnzMeErM

This logic should work in your case.

The set expression used in the video: Sum({1<Season_number={'$(=Season_number-2)'}>}Value)

Regards

Re: 1 Filter for 2 Pivot tables - Set Analysis

Jaquin — Wed, 13 Jul 2022 14:17:33 GMT

That already helped alot. Do you also know what i can do to implement more filters that shall affect the sum when i use them? So far only the season affects the sum of my value. If i change the 1 into a $ it wont give me 0 .

I have more filters that shall affect the sum but they do not need to have any specifications. Just the values i choose shall have impact in addition to the preseason part.

Re: 1 Filter for 2 Pivot tables - Set Analysis

Gui_Approbato — Thu, 14 Jul 2022 13:54:35 GMT

Good!

I think you can explore the PDF there is on this page about set analysis.

It will help you ignore some filters and consider others.

Check it out, it is a great material:

https://community.qlik.com/t5/QlikView-Documents/Set-Analysis-syntaxes-examples/ta-p/1491810

There is also a french version:

https://community.qlik.com/t5/Groupe-des-Utilisateurs-Francophones/Sets-Analysis-syntaxes-exemples/td-p/1492392