Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
billuran
Partner - Creator
Partner - Creator

Count Distinct Days between Dates Set Analysis

I have locationIDs, I am trying to count the total number of distinct days in date range that the Locations occur and divide by the total date range selected.

IE If I filter August 2018, I should see 31 days total but I only see 25 because those are the only dates in my data set.

count(TOTAL DISTINCT [Start Date])

So i tried this

((max(total [Start Date])-min( total [Start Date]))+1)

Now here is the kicker.

I want to select any or all day of week in a filter pain so I created this.

((((max(total [Start Date])-min( total [Start Date]))+1)/7*(count(distinct [Day of Week])))

This should give me 23, but it gives me 22.14

 

How can i get the distinct dates for a date range regardless of my Location IDs date occurrence, but yet filter my Location ID counts by date range?

 

2 Replies
anseglko
Creator
Creator

Wrapping 

((((max(total [Start Date])-min( total [Start Date]))+1)/7*(count(distinct [Day of Week])))

in ceil()-function gives 23 instead of 22.14

billuran
Partner - Creator
Partner - Creator
Author

This works, however if there is no data for a date that date is not counted...I would like to still have the total days in the date range.