topic Re: Set Analysis in Pivot Table in QlikView

Set Analysis in Pivot Table

fineasfalco — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Set Analysis in Pivot Table

sunny_talwar — Wed, 19 Jun 2019 17:11:45 GMT

Try this

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

Re: Set Analysis in Pivot Table

fineasfalco — Wed, 19 Jun 2019 17:32:08 GMT

Great, Sunny_talwar.

the solution is good.

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

Thank very much.

Re: Set Analysis in Pivot Table

fineasfalco — Thu, 20 Jun 2019 10:30:29 GMT

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.

Re: Set Analysis in Pivot Table

sunny_talwar — Thu, 20 Jun 2019 10:39:26 GMT

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

Re: Set Analysis in Pivot Table

fineasfalco — Thu, 20 Jun 2019 11:15:25 GMT

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.

Re: Set Analysis in Pivot Table

sunny_talwar — Thu, 20 Jun 2019 11:29:01 GMT

Then you have to use Aggr()