Set analysis - daily and mtd totals

[markp201]

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

[sunny_talwar]

What was the expression that you tried?

[markp201]

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]

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

All formats are numeric.  Returns 0  

[markp201 · Accepted solution]

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