count in sub aggr in pivot chart on total selection

[#1]

Hello,

I have  this type of data:

Product	MainID	SubID
A	1	12
A	1	4
A	1	4
A	2	13
A	3	4
B	1	12
C	2	5
C	1	7
C	1	9

I have a pivot with Product dimension and a calculated dimension using some rank function which give a list of SubID.

I want an expression for each line of my chart to display the count of distinct SubID in the MainID associated to the SubID of the line.

as below:

Product	Ranked SubID	Count Sub ID
A	4	4
	12	4
	13	2
B	12	4
C	5	2
C	7	4
C	9	4

for example in total selected data i have 4 Sub ID in the MainID "1", so for the line Product "A" and Sub ID "4" I have associated MainID "1" and I want display the total  count of SubID for this MainID. Here 4.

I guess it's possible using aggr, total and set analysis, but I can't figure the good expression.

Could you help me?

Thanks.

[swuehl · Accepted solution]

Maybe like attached?

Regards,

Stefan

[#3]

It solve it.

Thanks you very much.

Just for my understanding, could you explain how it work?

The Total<MainID> mean take all MainID in selection not just MainID of the row or dimension level.

If i correctly understand it:

1. It count distinct SubID by Product and MainID (aggr(X,Product,mainID)).
2. For all selected MainID (Total<MainID>).
3. Then the pivot dimension restrict result only on current row (->only one MainID).

Or it's the aggr() dimensions which restrict only row level count to current MainID in aggregating the global count by all selected MainID?

And why the Max function?

Regards,

Sylvain

[swuehl]

Sylvain,

I will try to explain:

The aggr() function could be considered as a table within the expression, so maybe let's start with creating a separate chart object (e.g. a pivot table) with dimensions product, MainID and SubID and expression = count(total<MainID> distinct SubID)

The Total<MainID> mean take all MainID in selection not just MainID of the row or dimension level.

No, that's not correct, from the Help:

The total qualifier may be followed by a list of one or more field names within angle brackets. These field names should be a subset of the chart dimensions. In this case the calculation will be made disregarding all chart dimensions except those listed, i.e. one value will be returned for each combination of field values in the listed dimension fields.

So, it's the other way round: Group by MainID and aggregate over Product and SubID.

This inline expression table we created is now restricted to the row values of Product and SubID, thus for Product = A and SubID=12 we get 4, for Product=A and SubID=13 we get 2.

The combination of Product=A and SubID=4 is however ambiguous, this combination shows to MainID: 1 and 3.

So we get two values for the distinct count per MainID: 1 and 4.

I decided to choose the max value to match your sample attached to your OP, but this might be an assumption that is not correct, so you could also sum the single results for combination using sum() instead of max(), or show both values using concat().

Hope this explains my approach better,

Stefan

[#5]

Hello,

It helps me.

Thanks.

Sylvain