total line to show total of count distincts in rows

[#1]

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

[sunny_talwar]

Try this as your expression:

Sum(Aggr(Count(DISTINCT TransDate), Territory))

[Anonymous]

In expression tab,there is an option called Total Mode, you can select the option Sum of Rows

[#4]

Thanks Sunny, unfortunately, that still gives distinct counts of dates, but at Territory level.

[sunny_talwar]

Good advice, If I were you, I would do it this way.

[tobivogt]

Hi,

if you need this in a chart - would total mode "sum" meet your needs?

It's on the Expressions-Tab:

Regards,

Tobias

[#7]

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.

[sunny_talwar]

For the pivot table you would have to use the Aggr expression I provided:

Sum(Aggr(Count(DISTINCT TransDate), Territory))