Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Partner
Partner

Re: Generate Sum When Comparing Dates at Regular Intervals

where is the specific recurring dates stored?

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

Highlighted
Contributor II
Contributor II

Re: Generate Sum When Comparing Dates at Regular Intervals

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

Highlighted
Partner
Partner

Re: Generate Sum When Comparing Dates at Regular Intervals

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)

Highlighted
Contributor II
Contributor II

Re: Generate Sum When Comparing Dates at Regular Intervals

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?