Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Qliker
Contributor II
Contributor II

Generate Sum When Comparing Dates at Regular Intervals

I have a table displaying CUSTOMER and valid LICENSES in the form of a STARTING DATE and an END DATE (date interval). A license is always valid over a specific time period, e.g. from 1 January 2020 to 31 January 2020.
Now, I want to automatically generate a table that shows me the number of valid licenses for certain recurring appointments (specific dates), e.g. 1 January 2020, 8 January 2020, 15 January 2020 etc.

More plainly: Number of valid licenses at specific RECURRING DATES, i.e. whenever the RECURRING DATE is greater than STARTING DATE and less than END DATE.

How should I approach with SET ANALYSIS for this?

4 Replies
dplr-rn
Partner - Master III
Partner - Master III

where is the specific recurring dates stored?

or is just arbitrary and you want to include in set analysis?

Qliker
Contributor II
Contributor II
Author

It's just arbitrary! I want to generate it within Qlik.

dplr-rn
Partner - Master III
Partner - Master III

not completly clear how you get the date but general syntax is something below

 

Count({<LicenseEnd={">=$(=Date(Date#('01/01/2020','MM/DD/YYYY'),'MM/DD/YYYY'))"},

LicenseStart={"<=$(=Date(Date#('01/01/2020','MM/DD/YYYY'),'MM/DD/YYYY'))"},

>}ID)

Qliker
Contributor II
Contributor II
Author

Thanks!

 

Date is a variable (see Screenshot). How should I integrate it into the formula? 

sum({$<Enddate={">=Date"}, Startdate={"<=Date"}>} Licenses)

Returns 0 

What's wrong?