Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the data like this in front end.
ID | Month | Sales |
A | Sept | 10 |
B | Sept | 11 |
C | Sept | 12 |
DD | Sept | 13 |
A | Oct | 14 |
B | Oct | 15 |
C | Oct | 16 |
EE | Oct | 17 |
A | Nov | 18 |
B | Nov | 19 |
C | Nov | 20 |
DD | Nov | 21 |
EE | Nov | 22 |
FF | Nov | 23 |
Here I am comparing Max month (Nov - static) with previous months (Dynamic).
I need to filter IDs which are present in Max month and not present in Previous month.
Example cases:
Previous month selection: Sept
Expected output: Sales of EE + FF = 22 + 23 = 45
Previous month selection: Oct
Expected output: Sales of DD + FF = 21 + 23 = 44
Please help me in writing set analysis expression. Since the selection is dynamic, I need to write expression in front end only.
Hi
Try like below
=Sum({<Month={'Nov'}, ID =e(ID)>}Sales)
Hope, you will select the Month to verify the result
Hi
Try like below
=Sum({<Month={'Nov'}, ID =e(ID)>}Sales)
Hope, you will select the Month to verify the result
@MayilVahanan Hi, if we select any of previous month, its working. But my case is a bit different. As per my question, the above answer is correct.
Suppose if there are only two months, both are static (not dynamic) and there wont be any selection on month.
A | Oct | 14 |
B | Oct | 15 |
C | Oct | 16 |
EE | Oct | 17 |
A | Nov | 18 |
B | Nov | 19 |
C | Nov | 20 |
DD | Nov | 21 |
EE | Nov | 22 |
FF | Nov | 23 |
Lets say only Oct and Nov data, then how to get the above required value using set analysis.
Expected output: Sales of DD + FF = 21 + 23 = 44 (IDs which are not present in Nov).
Hi
If its static, then try like below
=Sum({<Month={'Nov'}, ID =e({<Month={'Oct'}>}ID)>}Sales)
And, you can change the Nov and Oct without hardcode, try like below
LOAD *, Num#(Date(Date#(Month, 'MMM'),'MM')) as MonthNum INLINE [
ID, Month, Sales
A, Sep, 10
B, Sep, 11
C, Sep, 12
DD, Sep, 13
A, Oct, 14
B, Oct, 15
C, Oct, 16
EE, Oct, 17
A, Nov, 18
B, Nov, 19
C, Nov, 20
DD, Nov, 21
EE, Nov, 22
FF, Nov, 23
];
And try like below
=Sum({<MonthNum={'$(=Max(MonthNum))'}, ID =e({<MonthNum={'$(=Max(MonthNum)-1)'}, Month=>}ID)>}Sales)
Thank you @MayilVahanan for your time. My actual requirement is a bit different. Your solution helped me to achieve that.