Set Analysis in Pivot Table

[fineasfalco]

Hi Qlik community,

I have a problem with a complex set analysis expression within a pivot table.

To better describe my problem, I have created an example.

I have the following test data:

TEST:
LOAD * INLINE [MyPeriod, MyReferencePeriod, MyProduct, MyQty
"2019'Q1", "2018'Q1", "A1", 100
"2019'Q1", "2018'Q1", "A2", 100
"2019'Q1", "2017'Q1", "A3", 100
"2018'Q4", "2017'Q1", "A1", 100
"2018'Q4", "2016'Q1", "A2", 100
"2018'Q4", "2016'Q4", "A3", 100
];

I added this data to a pivot chart where the MyPeriod  dimension is a column field,

I have created a simple espression sumQty = sum(myQty)

I want to define a new expression NewSumQty that calculates the sum of  quantity  for each product in which MyReferencePeriod is maximum for rolling period (MyPeriod).

That is

NewSumQty  = A1 Qty + A2 Qty  (200pcs)   with MyPeriod = 2019'Q1  (Max of MyReferencePeriod is equal to   2018'Q1)

NewSumQty  = A1 Qty  (100 pcs)                     with MyPeriod = 2018'Q4 (Max of MyReferencePeriod is equal to 2017'Q1)

To implement this I must use the set analysis features.

I tried to implement it but the expression is not performed correctly.

i.e  NewSumQty = sum({$<[MyReferencePeriod] = {"$(='Aggr(Maxstring([MyReferencePeriod]),[MyPeriod])')"}>} [MyQty])

Could you help me to implement this expression?

Thank you very much for your support.

Regards

fineas

[sunny_talwar · Accepted solution]

Try this

Sum(Aggr(
If(MyReferencePeriod = MaxString(TOTAL <MyPeriod> MyReferencePeriod), Sum(myQty))
, MyPeriod, MyReferencePeriod))

[fineasfalco]

Great, Sunny_talwar.

the solution is good.

I 'll try to use your solution to my most complex expression.

Thank very much.

[fineasfalco]

Thank you for your prompt reply.

The proposed solution allows to achieve the result in a different way, but It 's possible get the same result with set analysis approach.

My problem showed is a simplification . I use a more complex set analysis expression.

I hope that exist a set analysis method, in this case the Max of MyReferencePeriod is a parameter of my set analysis espression.

[sunny_talwar]

You can create a flag in the script to do this using set analysis, but without further manipulation in the script... this isn't possible

[fineasfalco]

my doubt is how to do it, because I don't  want to precalculate the maximum and put it in a variable so I can use the value as a static parameter in my set analsysis espression, in fact I want the maximum of MyReferencePeriod to be contextual to MyPeriod, I want the parameter to change when MyPeriod changes, MyPeriod is a field column in a pivot table. My goal that the parameter maximum of MyReferencePeriod is calculated within my espression.

I hope I was clear.

[sunny_talwar]

Then you have to use Aggr()