1 Filter for 2 Pivot tables - Set Analysis

[Jaquin]

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.

[Jaquin]

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?

[MartW]

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

[Gui_Approbato]

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.

[Jaquin]

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?

[Jaquin]

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.

[Gui_Approbato · Accepted solution]

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

[Jaquin]

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.

[Gui_Approbato]

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/t...