topic Re: Calculate unique count of lower level within higher level in App Development

Calculate unique count of lower level within higher level

Braveen — Tue, 23 May 2023 17:00:30 GMT

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.

Re: Calculate unique count of lower level within higher level

Qrishna — Tue, 23 May 2023 17:27:27 GMT

for now ill provide two ways to achieve this,

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.

Re: Calculate unique count of lower level within higher level

Dementor31 — Tue, 23 May 2023 18:19:49 GMT

You can try this

Count(total <Zone> distinct State)

Re: Calculate unique count of lower level within higher level

Braveen — Wed, 24 May 2023 06:38:49 GMT

@Qrishna

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.

Re: Calculate unique count of lower level within higher level

marcus_sommer — Wed, 24 May 2023 07:47:46 GMT

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.

Re: Calculate unique count of lower level within higher level

Braveen — Wed, 24 May 2023 08:31:39 GMT

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

Re: Calculate unique count of lower level within higher level

marcus_sommer — Wed, 24 May 2023 08:33:38 GMT

Then: count(total < Zone> Field)

Re: Calculate unique count of lower level within higher level

Braveen — Wed, 24 May 2023 08:36:50 GMT

count(total < Zone> Field) is working.

Is < Zone> some advanced set analysis?

Re: Calculate unique count of lower level within higher level

Braveen — Wed, 24 May 2023 08:41:30 GMT

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

Re: Calculate unique count of lower level within higher level

marcus_sommer — Wed, 24 May 2023 08:50:04 GMT

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.

Re: Calculate unique count of lower level within higher level

marcus_sommer — Wed, 24 May 2023 08:51:50 GMT

If you want to ignore selections you may apply something like:

count({< State >} total <Zone> Field)

Re: Calculate unique count of lower level within higher level

Braveen — Wed, 24 May 2023 09:07:52 GMT

Tried this: count({< State >} total <Zone> Field), After applying the filter for state, metrics go blank for all states but the filtered value. Meaning, all states are still visible even after I applied the filter with correct count of states within the zone

Tried this too, aggr(count( {<State=>}distinct State), Zone) and this is giving me desired result.