More date fun

[463]

My situation is that I want to display something of a switch with dates in a combo chart (bars and lines).  

If the switch is in the 'A' position, I want to display the values that are in the last file or data set I received for each month from the previous XX months.  If in the 'B' position, I want to display all of the values from the files that I received in the current month.

With Apr as my current month:

Switch A:
Jan  Feb  Mar  Apr

Switch B:
1Apr  7Apr  10Apr  29Apr

And then when May comes around, Switch A would include May, Switch B would be the files loaded in May. No selections or filtering at this stage.  But, let me expand this just a bit as I am thinking for Switch B would be dedicated to show the files loaded in a 'selected' month (I don't want to say drill down but, in essence, that is what is being done.).

I currently have two date fields in my table.  One being the LoadDate (YYYYMMDD) and the other being the LoadMonth (YYMMM). I have one variable to capture the current month.

What would be the expression to filter the last file's values for a month to satisfy Switch A? Is this possible?

What would be the expression to filter the data for the current month to satisfy Switch B?  Is this possible?
= Sum({1<LoadMonth = {"='=vCurrentMonth'"}>} X - Y - Z)
I started with the above expression, but this now doesn't return anything.  I tried to filter or parse the LoadMonth with Date(Date#(LoadMonth,'YYMMM'),'YYMMM') but this didn't help.  What I am doing wrong here?

TIA!

[463]

Just wanted to add that I think I got Switch B figured out:

Sum({1<LoadDate={">$(vGetLastMonthEndDT) <$(vGetNextMonthStartDT)" }>} X - Y - Z)

[ysalvi43]

Hello @463 

Please find below script and output screenshot for your refernce, In this method we have created flag for max date of every month and use that flag in the front end for filtering purpose
Inline table 
DataFiles:
LOAD
Date(Date#(LoadDate,'YYYYMMDD'),'DD-MMM-YYYY') as LoadDate,
Date(MonthStart(Date(Date#(LoadDate,'YYYYMMDD'))),'YYMMM') as LoadMonth,
X,
Y,
Z
INLINE [
LoadDate,X,Y,Z
20260105,100,10,5
20260112,120,15,8
20260128,150,20,10
20260203,130,12,7
20260210,145,14,8
20260225,170,18,9
20260304,180,22,11
20260311,200,25,12
20260327,230,30,15
20260401,210,28,14
20260407,240,32,18
20260410,260,35,20
20260429,300,40,25
];

MaxMonthDate:
LOAD
LoadMonth,
Max(LoadDate) as MaxLoadDate
Resident DataFiles
Group By LoadMonth;

LEFT JOIN (DataFiles)
LOAD
LoadMonth,
MaxLoadDate
Resident MaxMonthDate;

FinalData:
LOAD *,
If(LoadDate = MaxLoadDate,1,0) as IsLatestFile
Resident DataFiles;

DROP TABLE MaxMonthDate;
DROP TABLE DataFiles;

Front end logic: Sum({<IsLatestFile={1}>} X-Y-Z)

Thanks