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