Set Analysis

[NemoAndStitch22]

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

[Prashant_Naik]

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

[sasiparupudi1]

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

[laila20]

sum({$<Year={"$(=Year(today()))"},Month={"$(=Month(today()))"}>}Value)