Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

adrianfer
New Contributor III

Set Expression for counting dates

Hi guys! 

I have a sample data here with Start and End Dates. In the Qlik app, I would have a variable input for: EmployeeId, Date From and Date To. I need help in counting the days that the employee is in a location at a given time.

Table_Range:
LOAD * INLINE
[
EmployeeId,StartDate,EndDate,Location
111,2/8/19,5/8/19,AAA
111,6/8/19,15/8/19,BBB
111,16/8/19,20/8/19,CCC
](delimiter is ',');

 

Table_Range:

EmployeeIdStartDateEndDateLocation
1112/8/195/8/19AAA
1116/8/1915/8/19BBB
11116/8/1920/8/19CCC

 

Sample Date From and Date To input and the outputs:

Date From2/08/2019  
Date To22/08/2019  
EmployeeIdDays in AAADays in BBBDays in CCC
1114105
    
    
Date From1/08/2019  
Date To17/08/2019  
EmployeeIdDays in AAADays in BBBDays in CCC
1114102
    
    
Date From10/08/2019  
Date To30/08/2019  
EmployeeIdDays in AAADays in BBBDays in CCC
111065

 

Thank you.

Labels (2)
1 Solution

Accepted Solutions
Partner
Partner

Re: Set Expression for counting dates

Assuming variables vstart and vto for the dynamic date period, here is the formula:

=rangemax(0,
rangemin(only({<Location={'AAA'}>}EndDate),num(vto))
-
rangemax(only({<Location={'AAA'}>}StartDate),num(vstart))
+1)

You change the Location value for the other columns.

There is no need from EmployeeId, you can see them all. Else, just add the selection to the two set analysis modifiers.

View solution in original post

5 Replies
MVP & Luminary
MVP & Luminary

Re: Set Expression for counting dates

In most use-cases it's not possible to calculate it in the UI just with more or less advanced set analysis else it would also need to use aggr-functions and maybe even nested ones. That's not only quite complex else usually rather slowly, too.

The most common approach is therefore to resolve the from-to dates within the script to a real data. The backgrounds for it could you find here: IntervalMatch.

- Marcus

adrianfer
New Contributor III

Re: Set Expression for counting dates

I don't think Interval match would do...

How would this be done in the UI?

MVP & Luminary
MVP & Luminary

Re: Set Expression for counting dates

The general approach of resolving numeric intervals with an intervalmatch or alternative with a while-loop worked usually very well and it's quite easy to implement. To solve such a task within the UI is much more complicated and therefore you shouldn't go this way unless you had ensured that intervalmatch didn't provide the needed results.

Therefore my suggestion just to try it. Very useful is often to do it at first in a dummy-application with a reduced dataset on records and fields to comprehend the logic and after that you could transfer the knowledge into your target-application.

- Marcus

adrianfer
New Contributor III

Re: Set Expression for counting dates

What i initially did is have a while loop so i can do a simple and normal count in the UI (https://community.qlik.com/t5/New-to-Qlik-Sense/Help-with-joining-two-tables/m-p/1609163#M142710)...... that created such a big dataset. Hence, i am looking for another approach of doing the more complicated calculation in the UI.

 

Partner
Partner

Re: Set Expression for counting dates

Assuming variables vstart and vto for the dynamic date period, here is the formula:

=rangemax(0,
rangemin(only({<Location={'AAA'}>}EndDate),num(vto))
-
rangemax(only({<Location={'AAA'}>}StartDate),num(vstart))
+1)

You change the Location value for the other columns.

There is no need from EmployeeId, you can see them all. Else, just add the selection to the two set analysis modifiers.

View solution in original post