topic Re: Set Analysis Using Date Range in App Development

Set Analysis Using Date Range

tomdon14 — Thu, 28 May 2020 19:50:50 GMT

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.

Re: Set Analysis Using Date Range

jwjackso — Thu, 28 May 2020 20:10:46 GMT

I think you have an extra equal sign:

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

Re: Set Analysis Using Date Range

tomdon14 — Thu, 28 May 2020 20:21:44 GMT

Thanks Jwjackso, but still results in 0.

Re: Set Analysis Using Date Range

jwjackso — Thu, 28 May 2020 20:42:48 GMT

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:

Re: Set Analysis Using Date Range

tomdon14 — Thu, 28 May 2020 20:50:03 GMT

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

Re: Set Analysis Using Date Range

jwjackso — Thu, 28 May 2020 21:03:18 GMT

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.

Re: Set Analysis Using Date Range

tomdon14 — Thu, 28 May 2020 21:37:14 GMT

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?

Re: Set Analysis Using Date Range

jwjackso — Fri, 29 May 2020 15:20:10 GMT

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:

Re: Set Analysis Using Date Range

tomdon14 — Fri, 29 May 2020 20:27:04 GMT

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!