topic Re: Set Analysis – How to refer to a dimension's value? in QlikView

Set Analysis – How to refer to a dimension's value?

patricio — Mon, 07 Aug 2017 19:35:13 GMT

I have an input table with 3 fields: Month1, Month2 and Amount.

Month1	Month2	Amount
1	1	1
1	2	1
1	3	1
1	4	1
2	2	2
2	3	2
2	4	2
2	5	2
3	3	3
3	4	3
3	5	3
3	6	3

I need to create a straight table with 1 dimension (Month2) and one Expression based on Amount.

For every Month2, I need to calculate the Amount corresponding to Month2 and Month1= F(Month2).

As an example lets say that for every Month2 , I need the Sum of Amount where Month1=Month2 -1 .

Month2	Amount for Month2= Month1-1	Explanation
1	0	Amount for Month2=1 and Month1 = 0
2	1	Amount for Month2=2 and Month1 = 1
3	2	Amount for Month2=3 and Month1 = 2
4	3	Amount for Month2=4 and Month1 = 3
5	0	Amount for Month2=5 and Month1 = 4
6	0	Amount for Month2=6 and Month1 = 5

For doing this I thought in using a set analysis expression like the one below which does not work.

Sum({<Month1 = {$(=Only(Month2)-1)}>} Amount)

This expression does not work because it seems that the dimension is not considered in the dollar sign expansion. So the “Only” expression is returning a null value.

Could someone give me an idea on how to refer to my Month2 dimension in order to use it in the set analysis expression to obtain the required value of Month1?

Thanks

Re: Set Analysis – How to refer to a dimension's value?

sunny_talwar — Mon, 07 Aug 2017 19:40:01 GMT

May be this

=Sum(Aggr(If(Month2 - 1 = Month1, Amount), Month1, Month2))

Re: Set Analysis – How to refer to a dimension's value?

patricio — Mon, 07 Aug 2017 20:09:57 GMT

Hi Sunny,

Your response is really helpful and it does the job. I will just want to ask you if you think that there is a way to solve this by using set analysis instead of the IF?

Re: Set Analysis – How to refer to a dimension's value?

sunny_talwar — Tue, 08 Aug 2017 11:09:38 GMT

Here are few ways where you can use set analysis, but all of them require some intervention in script

1) Create a flag in the script where Month2 - 1 = Month1

Facts:

LOAD *,

If(Month2 - 1 = Month1, 1, 0) as Flag;

LOAD * INLINE [

Month1 , Month2 , Amount

1 , 1 , 1

1 , 2 , 1

1 , 3 , 1

1 , 4 , 1

2 , 2 , 2

2 , 3 , 2

2 , 4 , 2

2 , 5 , 2

3 , 3 , 3

3 , 4 , 3

3 , 5 , 3

3 , 6 , 3

];

and then use this expression

=Sum({<Flag = {1}>}Amount)

2) Create a unique field which combines Month2 & Month1 into a single field.

Facts:

LOAD *,

If(Month2 - 1 = Month1, 1, 0) as Flag,

AutoNumber(Month1&Month2) as Key;

LOAD * INLINE [

Month1 , Month2 , Amount

1 , 1 , 1

1 , 2 , 1

1 , 3 , 1

1 , 4 , 1

2 , 2 , 2

2 , 3 , 2

2 , 4 , 2

2 , 5 , 2

3 , 3 , 3

3 , 4 , 3

3 , 5 , 3

3 , 6 , 3

];

and then this expression

=Sum({<Key = {"=Month2 - 1 = Month1"}>} Amount)

Re: Set Analysis – How to refer to a dimension's value?

patricio — Tue, 08 Aug 2017 12:03:57 GMT

Thanks Sunny,

I was expecting for a "magical" way to appear that would only be using Set Analysis but it seems that it is no possible.

For the moment I will go with your recommendations that I really appreciate.

Re: Set Analysis – How to refer to a dimension's value?

sunny_talwar — Tue, 08 Aug 2017 12:07:26 GMT

The problem is that the set analysis is evaluated once per chart and doesn't change based on dimension. In your case, you want the set analysis to be evaluated differently for each dimension which is not possible.

Re: Set Analysis – How to refer to a dimension's value?

patricio — Tue, 08 Aug 2017 12:11:55 GMT

I would say that it is the dollar sign expansion that it is not considering the dimension. It is evaluated only once per chart disregarding the dimension.