Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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 |
Sample Date From and Date To input and the outputs:
Date From | 2/08/2019 | ||
Date To | 22/08/2019 | ||
EmployeeId | Days in AAA | Days in BBB | Days in CCC |
111 | 4 | 10 | 5 |
Date From | 1/08/2019 | ||
Date To | 17/08/2019 | ||
EmployeeId | Days in AAA | Days in BBB | Days in CCC |
111 | 4 | 10 | 2 |
Date From | 10/08/2019 | ||
Date To | 30/08/2019 | ||
EmployeeId | Days in AAA | Days in BBB | Days in CCC |
111 | 0 | 6 | 5 |
Thank you.
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.
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
I don't think Interval match would do...
How would this be done in the UI?
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
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.
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.