Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
jamesmf1982
Contributor III
Contributor III

Set Analysis with Variables

Good afternoon,

I was hoping for some help on variables in Set Analysis. I have created a new new variable to calculate the days between today() and the review date of a document:

vEventDuetoToday;  num(DocumentDueDate)-num(today())

 I also created a variable for v30Days which is num(30).

This works all fine, however I need to count the number of documents that are due to expire within 10 to 30 days, and then documents that are past their due date. The below Set Analysis works for counting all documents I have, but I need to modify it to count those as per the above. I've looked around but am a bit stuck. Any help would be great:

count({$<DocumentCaseType={'Care Needs Assessment 1'},DocumentOverallStatus={'InProgress'},
DocumentDocumentTemplateName={'CNA*'} >}DocumentCaseNumber)

I tried adding in $(v30Days) = {">=$(vEventDuetoToday)"} but that just broke it.

Any help would be great 🙂

1 Solution

Accepted Solutions
JuanGerardo
Partner
Partner

Hi @jamesmf1982, you can use following sintax in your set analysis:

count({$<DocumentDueDate={"<=$(=Today() + 30)"}DocumentCaseType={'Care Needs Assessment 1'},DocumentOverallStatus={'InProgress'},
DocumentDocumentTemplateName={'CNA*'} >} DocumentCaseNumber)

Maybe you need to use also a Date() function to format properly the due date, like $(=Date(Today() + 30, 'MM/DD/YYYY'))depending on your default date format.

JG

View solution in original post

2 Replies
JuanGerardo
Partner
Partner

Hi @jamesmf1982, you can use following sintax in your set analysis:

count({$<DocumentDueDate={"<=$(=Today() + 30)"}DocumentCaseType={'Care Needs Assessment 1'},DocumentOverallStatus={'InProgress'},
DocumentDocumentTemplateName={'CNA*'} >} DocumentCaseNumber)

Maybe you need to use also a Date() function to format properly the due date, like $(=Date(Today() + 30, 'MM/DD/YYYY'))depending on your default date format.

JG

View solution in original post

jamesmf1982
Contributor III
Contributor III
Author

Thanks for that, worked perfectly with the date addition