Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
patricio
Contributor III
Contributor III

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

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

1 Solution

Accepted Solutions
sunny_talwar

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)

View solution in original post

6 Replies
sunny_talwar

May be this

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

Capture.PNG

patricio
Contributor III
Contributor III
Author

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?

sunny_talwar

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)

patricio
Contributor III
Contributor III
Author

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.

sunny_talwar

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.

patricio
Contributor III
Contributor III
Author

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.