Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
kbush
New Contributor II

WTD count not equal to sum of daily counts

Daily counts are accurate but unable to get the correct weekly count. Also, need MTD and YTD, but that'll be step 2.

I'm counting JobID (414-001, 414-002, etc.), which can run more than once a day and more than one day a week. For example, a JobID runs 3 times on Mon, and once on Fri for a WTD count of 4. However, my WTD set analysis counts only one.

Also, I send the report a previous day filter in NPrinting and use  _History={1} to get all days of the week. _History is in the data load and is simply a flag for date < today.  If I send the Weekstart as a Filter, my WTD calc works, but I can't use that due to requirements of other sections of the report that only show previous day. 

When counting by weekday, the total JobID is 165.  However, set analysis returns 137 for WTD. Drilling into the table, the records are there to get to 165. 

I understand why I'm getting 137 in my WTD (Sun - Sat all have the same Weekstart date) calc, but can't figure out how to fix it. In my AGGR calc, if I use ProdDate or Weekday as the Dimension, I only get values for the ProdDate I send as a filter. 

Simple fix is to repeat the Weekday formula for Sun, Mon, Tue, etc. in the WTD, but that's clunky. 

Sun = 15

Mon = 15

Tue = 24

Wed = 24

Thu = 27

Fri = 41

Sat = 19

WTD = 137 (should be 165)

 

Daily Script

Count({$<Weekstart={'$(=Max( {$<_History={1}>} Weekstart))'},
[ProdDate]=,
Weekday= {"Sun"},
[Department]={"X02"}  >} [JobID] )

 

WTD

Sum(Aggr(Count({$<Weekstart={'$(=Max( {$<_History={1}>} Weekstart))'},

  [ProdDate]=,

[Department]={"X02"}  >} [JobID]), [Weekstart]))

1 Solution

Accepted Solutions
kbush
New Contributor II

Re: WTD count not equal to sum of daily counts

After a lot of trial and error, this solution worked 

Sum({$<[ProdDate]=>} Aggr(

{$<Weekstart={'$(=Max( {$<_History={1}>} Weekstart))'},
[ProdDate]=,
[Department]={"X02"}  >}

Count(Distinct [JobID]), [ProdDate]))

2 Replies
Channa
Contributor III

Re: WTD count not equal to sum of daily counts

i think your Max(weekstart) taking One day

can you share some sample app or data

Channa
kbush
New Contributor II

Re: WTD count not equal to sum of daily counts

After a lot of trial and error, this solution worked 

Sum({$<[ProdDate]=>} Aggr(

{$<Weekstart={'$(=Max( {$<_History={1}>} Weekstart))'},
[ProdDate]=,
[Department]={"X02"}  >}

Count(Distinct [JobID]), [ProdDate]))