Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try this
Sum(Aggr(
If(MyReferencePeriod = MaxString(TOTAL <MyPeriod> MyReferencePeriod), Sum(myQty))
, MyPeriod, MyReferencePeriod))
Try this
Sum(Aggr(
If(MyReferencePeriod = MaxString(TOTAL <MyPeriod> MyReferencePeriod), Sum(myQty))
, MyPeriod, MyReferencePeriod))
Great, Sunny_talwar.
the solution is good.
I 'll try to use your solution to my most complex expression.
Thank very much.
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.
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
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.
Then you have to use Aggr()