Filter values based on two fields

[Lokesh_5045]

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.

[MayilVahanan · Accepted solution]

Hi

Try like below

=Sum({<Month={'Nov'}, ID =e(ID)>}Sales)

Hope, you will select the Month to verify the result

[Lokesh_5045]

@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).

[MayilVahanan]

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)

[Lokesh_5045]

Thank you @MayilVahanan for your time. My actual requirement is a bit different. Your solution helped me to achieve that.