Skip to main content
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?