Calculate unique count of lower level within higher level

[Braveen]

I have a table structure that looks like below, I would like to calculate count of states column.

I tried Aggr(count(distinct state), zone) which gives me result for one record per zone and remaining are blank.

Any idea on how to calculate this would be great.

[krishna_2644]

for now ill provide two ways to achieve this,

1.

Data:
Load * Inline [
Zone, State
East, State 1
East, State 2
East, State 3
East, State 4
East, State 5
East, State 6
East, State 7
West, State 8
West, State 9
West, State 10
West, State 11
West, State 12
West, State 13
North, State 14
North, State 15
];

left join(Data)
load Zone, count(distinct State) as [count of states]
resident Data
group by Zone;

2.  Not the best approach, but works if needed.

In dimensions: Zone, State,  =aggr(count(distinct State),Zone)

Expressions: 1

Presentation tab: Hide expression column

You get same result.

Hope this helps.

k

[Dementor31]

You can try this

 Count(total <Zone> distinct State)

[Braveen]

@krishna_2644  

This works. In dimensions: Zone, State,  =aggr(count(distinct State),Zone)

Why is it not the best approach?

Also, I don't follow this ( Expressions: 1, Presentation tab: Hide expression column)

Would help if you could explain.

[marcus_sommer]

aggr() creates an extra virtual table and requires a lot of resources - at least compared against native objects and normal aggregation measures. Therefore this feature should be only used if there is no other way to get the wanted view.

And in your case you don't need the aggr() neither within the dimension nor in the expression else applying a total statement to ignore the dimensionality of the object should be sufficient and an expression like: count(total Field) should be working.

[Braveen]

@marcus_sommer count(total Field) gives me total state count for all the zones, not zone wise state count.

[marcus_sommer · Accepted solution]

Then: count(total < Zone> Field)

[Braveen]

count(total < Zone> Field) is working.

Is < Zone> some advanced set analysis?

[Braveen]

@marcus_sommer  When I filter for one state, the number should remain same, now, it is changing to 1.

[marcus_sommer]

Total with or without the specifying of dimensions which should be not to consider is not a part of a set analysis which is a filtering against the data-set else it's a feature of the object-dimensionality which could be completely or partly ignored.