Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

chriys1337
Contributor II

Count days in Calendar disregarding connection to transaction data

Hi,

I am having in my datamodel one central transactional data / fact table, with Calls from employees.

Connected with this fact table is a master calendar which is connected to the date of these Calls.

Now I would like to build in the frontend a table for each employee which gives out the "amount of days per selected time period".

I have tried it with the formula count(distinct date), what happens it shows the amount of days (where each employee had Calls). But I would like to see the amount of days which are given in the selected time period, disregarding the calls.

Than I tried to use count( {<Name=, [Country Code]=>}distinct date), to disregard the calls, but that did not work out.

Does anybody have an idea, how to count ALL days in the calendar, per selected timeperiod but disregarding the calls ?

1 Solution

Accepted Solutions
Partner
Partner

Re: Count days in Calendar disregarding connection to transaction data

try adding "total".
7 Replies
Partner
Partner

Re: Count days in Calendar disregarding connection to transaction data

max(date)-min(date)?
chriys1337
Contributor II

Re: Count days in Calendar disregarding connection to transaction data

I have selected Q1, Jan to March 2019 which has 91 days in my calendar.
Employee A has made calls from 01.02 to 30.03 and Employee B had calls from 01.01 to 30.01 only.
In my table the result with that formula would be roughly:
Employee, "Working Days"
A, 60
B, 30

BUT I would like to see:
Employee, Working Days
A, 91
B, 91
Partner
Partner

Re: Count days in Calendar disregarding connection to transaction data

exclude the selections except for the period selections you want to make.
chriys1337
Contributor II

Re: Count days in Calendar disregarding connection to transaction data

I have not selected anything, in my table there are just 2 fields (employee, country) and this one measure [count(distinct date)].
I already tried to exclude the dimension employee / country: count( {<Name=, [Country Code]=>}distinct date)
But it did not work. The count(distinct date) shows me the amount of days where the employee did calls -´due to relationship between my calendar and the fact table. But I just want to display the amount of days / how many days has the selected time period (Jan to March 2019, 91 days).

I need to blend out the relationship from both tables, in this special case. But dont know how to do. 

 

Partner
Partner

Re: Count days in Calendar disregarding connection to transaction data

try adding "total".
chriys1337
Contributor II

Re: Count days in Calendar disregarding connection to transaction data

THANKS SO MUCH; how did you get this idea? 🙂
Partner
Partner

Re: Count days in Calendar disregarding connection to transaction data

knowing what it does. 🙂
I don't do these kind of calculations often but playing arround with the functions available you can get most things done.