Forum Discussion
Month value from Runing total
Hello Community, How can I extract month value if I have runing total to each month in year and ID? I know how to do it in Excel: =if(
mid([PERIOD];6;2)=1;[@RUNING_TOTAL];
SUMPRODUCT([RU...
Anonymous ,
Could you please clarify the logic of the expression below?
SUMPRODUCT([RUNING_TOTAL];1*([ID_PRODUCT]=[@ID]);1*([ID_REGION]=[@ID_REGION]);1*(mid([PERIOD];6;2)=(mid([@PERIOD];6;2)-1)))
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
In my example it will be the same as SUMIFS in excel after 2013. In general it helps multiply values with list of conditions.
For example:
We need multiply [Value_1]*[Value_2] where [Condition_1]="Value 1:&&[Condition_2]="Type 5"
| Condition_1 | Condition_2 | Value_1 | Value_2 |
| Value 1 | Type 5 | 4 | 2 |
| Value 2 | Type 2 | 5 | 8 |
| Value 1 | Type 1 | 3 | 7 |
| Value 3 | Type 4 | 4 | 2 |
| Value 4 | Type 5 | 1 | 1 |
The expression will be:
=SUMPRODUCT(1*([Condition_1]="Value 1]);1*([Condition_2]="Type 5");[Value_1];[Value_2])
As a result we will get matrix for multiply
| Condition_1 | Condition_2 | Value_1 | Value_2 | |
| 1 (Value 1 = Value 1 ► TRUE) | 1 (Type 5 = Type 5 ► TRUE) | 4 | 2 | 8 (1*1*4*2) |
| 0 (Value 2 <> Value 1 ► FALSE) | 0 | 5 | 8 | 0 (0*0*5*8) |
| 1 (Value 1 = Value 1 ► TRUE) | 0 | 3 | 7 | 0 (1*0*3*7) |
| 0 (Value 3 <> Value 1 ► TRUE) | 0 | 4 | 2 | 0 (0*0*4*2) |
| 0 (Value 4 <> Value 1 ► TRUE) | 1 | 1 | 1 | 0 (0*1*1*1) |
| RESULT: | 8 (8+0+0+0+0) |