Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Data:
Territory | TransDate | |
A | 20150101 | |
A | 20150102 | |
A | 20150102 | |
B | 20150101 | |
B | 20150102 | |
B | 20150102 | |
B | 20150103 |
Report:
Territory | Unique days |
A | 2 |
B | 3 |
Total | 3 (this is where i want '5' to be reported) |
The column Unique days is defined as count(distinct [TransDate]).
The report total line reports ‘3’, which is understandable, however what I want is for the total line to report ‘5’ (the total of the territory’s unique days)
Any ideas?
thanks
Try this as your expression:
Sum(Aggr(Count(DISTINCT TransDate), Territory))
In expression tab,there is an option called Total Mode, you can select the option Sum of Rows
Thanks Sunny, unfortunately, that still gives distinct counts of dates, but at Territory level.
Good advice, If I were you, I would do it this way.
Hi,
if you need this in a chart - would total mode "sum" meet your needs?
It's on the Expressions-Tab:
Regards,
Tobias
Hi,thanks for the suggestion, for this expression, "total Mode" is disabled (grayed out), with "Expression Total" selected. I switched to Straight Table, changed the Total mode as suggested, switched back to Pivot table, and the result is the same, still shows a count distinct in the total row.
For the pivot table you would have to use the Aggr expression I provided:
Sum(Aggr(Count(DISTINCT TransDate), Territory))