Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
May be this
=Sum(Aggr(If(Month2 - 1 = Month1, Amount), Month1, Month2))
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?
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)
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.
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.
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.