Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
Highlighted
Contributor 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

Tags (2)
1 Solution

Accepted Solutions
Highlighted
Contributor III

Re: Set analysis - daily and mtd totals

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
Highlighted

Re: Set analysis - daily and mtd totals

What was the expression that you tried?

Highlighted
Contributor III

Re: Set analysis - daily and mtd totals

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

Highlighted

Re: Set analysis - daily and mtd totals

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

Highlighted
Contributor III

Re: Set analysis - daily and mtd totals

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

All formats are numeric.  Returns 0  

Highlighted
Contributor III

Re: Set analysis - daily and mtd totals

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