Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hiya,
I'm sure this has already been asked Many times, but I can't find a question that gives me the answer I need..
I'm trying to do a set analysis based on the year being this year and the month being the latest month.
Currently this is my formula, (It doesn't work)
sum({$<Year={"2018"},Value,({$<Month={Max('SJL')}})})
But ideally I don't want to have to specify the Year and month as 2018 and January as I would have to change it every month.
Does anyone know how to correct my formula to make this work?
Thanks,
Megan
Hi,
This might help you,
Count( {<Year={"$(=Max(Year))"},Month={"$(=MaxString(Month))"}>} value)
I have used MaxString because my month are in String format, you can use Max(month) if your month field is in number format as the Max(month) returns the current month number.
Regards,
Prashant
You can do something like below using the current date
Year={"$(=Year(Today()))"} Set Year to the Current Year
Month={"$(=Date(MonthStart(Today(),-1),'MMM'))"} take the current date, read the Monthstart of the previous month (note -1)and format the result as month.
set analysis must always be with in the {<>} in your aggregation ex: sum(Value)
sum(
{$<Year={"$(=Year(Today()))"}, Month={"$(=Date(MonthStart(Today(),-1),'MMM'))"}>}
Value)
hth
Sas
sum({$<Year={"$(=Year(today()))"},Month={"$(=Month(today()))"}>}Value)