Set Analysis with current selections

[mfigueiredo]

Hi Qlik Community,

Although I've done research on this topic (and there are plenty on YTD, QTD here on the community) I can't seem to find a solution that helps me.

I have the following table, with data for each Quarter. I do not have a Quarter field in my Model, and the company doesn't have it as well.

Expressions:

Q1

sum({<Year={$(=max(Year))}, MarketCd=-{'PA','SPO','OF','AM','AC'}, Month={'1','2','3'}>} Price)

/

sum({<Year={$(=max(Year))}, MarketCd=-{'PA','SPO','OF','AM','AC'}, Month={'1','2','3'}>} Sales)

Q2:

sum({<Year={$(=max(Year))}, MarketCd=-{'PA','SPO','OF','AM','AC'}, Month={'4','5','6'}>} Price)

/

sum({<Year={$(=max(Year))}, MarketCd=-{'PA','SPO','OF','AM','AC'}, Month={'4','5','6'}>} Sales)

and the other expressions change ONLY the months as well.

My problem:

Data is loaded everyday; which means I already have data for June and July.

However, the specific reports I am creating should be sensible to user selections.

What I mean is: while I was making the May Report, June data was already available and included in that Q2 expression. But I don't want June data to be included if I did not select that month.

I need the Q2 expression (and all others) to ONLY include the months the user has selected. If I select the first 5 months, Q1 should represent the average for the first three months and Q2 for ONLY the fourth and fifth month.

I wasn't aware of this problem until now... Can you help me?

I was hoping to get an expression that solved this, since I only make the charts and do not edit the model or the script at all.

Thank you so much,

Mariana.

[Gysbert_Wassenaar · Accepted solution]

Try intersection with the default state:

sum({$*<Year={$(=max(Year))}, MarketCd=-{'PA','SPO','OF','AM','AC'}, Month={'4','5','6'}>} Price)

/

sum({$*<Year={$(=max(Year))}, MarketCd=-{'PA','SPO','OF','AM','AC'}, Month={'4','5','6'}>} Sales)

Or intersection with only the possible Month values from the default state:

sum({<Year={$(=max(Year))}, MarketCd=-{'PA','SPO','OF','AM','AC'}, Month={'4','5','6'}*P(Month)>} Price)

/

sum({<Year={$(=max(Year))}, MarketCd=-{'PA','SPO','OF','AM','AC'}, Month={'4','5','6'}*P(Month)>} Sales)

[mfigueiredo]

Thank you so much Gysbert.

The first expression works perfectly! I had tried the dollar sign but without the * and in the wrong place it seems.

Ashamed to find out how simple it was... then again I am a Qlik Newbie!

Best regards,

Mariana.