Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
adrianfer
Contributor III
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
gmenoutis
Partner - Creator II
Partner - Creator II

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
marcus_sommer

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
Contributor III
Contributor III
Author

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

How would this be done in the UI?

marcus_sommer

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
Contributor III
Contributor III
Author

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.

 

gmenoutis
Partner - Creator II
Partner - Creator II

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.