Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
WE ARE LISTENING! New Navigation for Qlik Community, Sept. 26: TELL ME MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
kbush
Contributor II
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
Contributor II
Contributor II
Author

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]))

View solution in original post

2 Replies
Channa
Specialist III
Specialist III

i think your Max(weekstart) taking One day

can you share some sample app or data

Channa
kbush
Contributor II
Contributor II
Author

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]))