sum(aggr(count(distinct id)),date1) for dynamic pivot table

amaaiia — Thu, 25 May 2023 10:20:37 GMT

Hi!

date1	field2	field3	id
25/05/2023	AAA	111	7
25/05/2023	AAA	111	8
26/05/2023	BBB	111	7
26/05/2023	AAA	222	8
26/05/2023	BBB	111	9

I have a pivot table with date1, field2 and field3 as columns, and I want a measure that gets the sum of distinct id's per date1. I've tried with sum(aggr(count(distinct id)),date1), but it doesn't work for the rows of field2 and field3, because they have to be included in the measure. The problem is that depending on the level of dimension, the measure should be one or another.

For example, for field2 it would be sum(aggr(count(distinct id)),date1, field2), and for field3, sum(aggr(count(distinct id)),date1, field2, field3). In the pivot table the dimensions are dynamic, the user can move the order of then, so the measure would also change.

The count(distinct id) of all data is 3, but the sum(aggr(count(distinct id)),date1) is 2+3=5.

How can I write the measure to be the same for all levels of the pivot table?

Re: sum(aggr(count(distinct id)),date1) for dynamic pivot table

Chanty4u — Thu, 25 May 2023 17:30:11 GMT

Try this

sum({<date1=, field2=, field3=, id=>} Aggr(Count(DISTINCT id), date1, field2, field3))

topic sum(aggr(count(distinct id)),date1) for dynamic pivot table in App Development

sum(aggr(count(distinct id)),date1) for dynamic pivot table

Re: sum(aggr(count(distinct id)),date1) for dynamic pivot table