topic Re: Set analysis - daily and mtd totals in QlikView

Set analysis - daily and mtd totals

markp201 — Wed, 04 Apr 2018 16:06:15 GMT

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.

sunny_talwar — Wed, 04 Apr 2018 16:16:20 GMT

What was the expression that you tried?

markp201 — Wed, 04 Apr 2018 16:30:53 GMT

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 — Wed, 04 Apr 2018 17:37:46 GMT

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

markp201 — Thu, 05 Apr 2018 21:11:42 GMT

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

All formats are numeric. Returns 0

markp201 — Thu, 05 Apr 2018 21:47:56 GMT

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