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: 
markp201
Creator III
Creator III

Set analysis - daily and mtd totals

I have to get the daily count and the mtd count in the same table

I select a single date and the daily count is straightforward but how can I get the MTD count?

Seems perfect for set analysis and I've tried the date range syntax but the count matches the daily.

Date range in set analysis

1 Solution

Accepted Solutions
markp201
Creator III
Creator III
Author

Ok - finally got it.

Average daily counts for month

vMonthStart       =NUM(MONTHSTART(INSPN_DATE))

vSelectedDate     =INSPN_DATE

vMTDDays          =NETWORKDAYS($(vMonthStart),$(vSelectedDate))

COUNT({<INSPN_DATE={'>=$(vMonthStart)<=$(vSelectedDate)'}>}INSPN_STATUS) / $(vMTDDays)

Only took a 3 days

View solution in original post

5 Replies
sunny_talwar

What was the expression that you tried?

markp201
Creator III
Creator III
Author

COUNT({1<INSPN_STATUS={'Complete'},INSPN_DATE={">=$(=NUM(MONTHSTART(INSPN_DATE)))<=$(=NUM(MONTHEND(INSPN_DATE)))"}>}INSPN_STATUS)

This is the field i use for the selection.

Do i need to use a variable instead of the actually field? 

In english - for each region I need the count for the selected date and the related MTD count

sunny_talwar

What is the format for INSPN_DATE? Is this formatted as a number?

markp201
Creator III
Creator III
Author

COUNT({<INSPN_DATE=,INSPN_DATE={">=$(vMonthStart)"}, INSPN_DATE={"<=$(vSelectedDate)"}>}INSPN_STATUS)

All formats are numeric.  Returns 0  

markp201
Creator III
Creator III
Author

Ok - finally got it.

Average daily counts for month

vMonthStart       =NUM(MONTHSTART(INSPN_DATE))

vSelectedDate     =INSPN_DATE

vMTDDays          =NETWORKDAYS($(vMonthStart),$(vSelectedDate))

COUNT({<INSPN_DATE={'>=$(vMonthStart)<=$(vSelectedDate)'}>}INSPN_STATUS) / $(vMTDDays)

Only took a 3 days