# App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for
Search instead for
Did you mean:
Contributor III

## Set Analysis Using Date Range

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.

1 Solution

Accepted Solutions
Specialist III

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:

8 Replies
Specialist III

I think you have an extra equal sign:

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

Contributor III
Author

Thanks Jwjackso, but still results in 0.

Specialist III

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:

Contributor III
Author

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

Specialist III

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.

Contributor III
Author

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?

Specialist III

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:

Contributor III
Author

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!

Tags