# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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:
[
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.

Labels (2)

• ### Set Analisys

1 Solution

Accepted Solutions
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.

5 Replies
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

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

## 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

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

## 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.