Set Analysis Using Date Range

[tomdon14]

Hello -

I'm trying to calculate a measure based on a date range using set analysis and not achieving the proper result.  When I select a month (or months) I would like to create a 4-month total of cases from the most recent month selected and previous 4 months.

I have 2 variables created:
v_max_month = most current month selected
v_max_month_minus3 = maximum month selected minus 3 months

The calculation to count cases for those 4 months is returning zero:
Count({<YearMonth={">=$(=v_max_month_minus3)"} >} CASE_AER_NUMBER)

Appreciate any insight. Thanks.

[jwjackso]

I think you have an extra equal sign:

Count({<YearMonth={">=$(v_max_month_minus3)"} >} CASE_AER_NUMBER)

[tomdon14]

Thanks Jwjackso, but still results in 0.

[jwjackso]

When you are in the expression editor, look at the bottom to see how the Set Analysis statement is being rendered to make sure it is working as expected.  I created a simple variable in an app:  Let vToday=Today(); 

Look at the top and bottom of the image below:

[tomdon14]

Thanks again. Expression is being evaluated as attached...

[jwjackso]

Based on how the variable is being evaluated at the bottom,

=Count({<YearMonth={">=$(=$(v_max_month_minus3))"}>} CASE_AER_NUMBER)

The inner $(v_max_month_minus3) evaluates to the expression you want to execute

The outer $(=$(v_max_month_minus3)) is required for the expression and needs the equal sign.

[tomdon14]

Thanks, that does seem to evaluate to the correct expression. However it is still returning 0 records. 

I tried testing the expression: 
Count({<YearMonth={"Nov-2019"} >} CASE_AER_NUMBER)
This does return records. Could there be some issue with the >= syntax in the evaluated expression?

[jwjackso · Accepted solution]

I think the problem will be related to the YearMonth field, is it a date or string?  I used the data below:

Let vDT=Date(Floor(Today()),'MMM-YYYY');

Data:
load Dept,
Date(Date#(DT,'MMM-DD-YYYY'),'MMM-YYYY') as DT,
Date(Date#(DT2,'MMM-YYYY'),'MMM-YYYY') as DT2,
DT3,
Amt;
Load * Inline [
Dept,DT,DT2,DT3,Amt
D100,'Jan-01-2020','Jan-2020',Jan-2020,100
D100,'May-01-2020','May-2020',May-2020,200
];

See the results below:

[tomdon14]

Thank you Jwjackso! I believe the problem WAS with the format of YearMonth. I did not change the data load but instead used a field "Date" that was already in the model. Please see the revised calculations below. Thanks again for your help!