Qlik Community

Ask a Question

Qlik Sense App Development

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

Announcements
March 11, 2PM EST: Do More with Qlik - Getting Started wtih Qlik Sense SaaS - Redux. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tomdon14
Contributor III
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.Capture.PNG

1 Solution

Accepted Solutions
jwjackso
Specialist II
Specialist II

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:

setanalysis.PNG

 

View solution in original post

8 Replies
jwjackso
Specialist II
Specialist II

I think you have an extra equal sign:

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

tomdon14
Contributor III
Contributor III

Thanks Jwjackso, but still results in 0.

Capture2.PNG

jwjackso
Specialist II
Specialist II

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:

variable.PNG

tomdon14
Contributor III
Contributor III

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

Capture3.PNG

jwjackso
Specialist II
Specialist II

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
Contributor III
Contributor III

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?

Capture4.PNG

jwjackso
Specialist II
Specialist II

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:

setanalysis.PNG

 

View solution in original post

tomdon14
Contributor III
Contributor III

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!

Capture5.PNG