Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
What was the expression that you tried?
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
What is the format for INSPN_DATE? Is this formatted as a number?
COUNT({<INSPN_DATE=,INSPN_DATE={">=$(vMonthStart)"}, INSPN_DATE={"<=$(vSelectedDate)"}>}INSPN_STATUS)
All formats are numeric. Returns 0
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