Total count for dimension when different dimension selected

[#1]

Hello all,

I have a table as follows:

Shop_ID,City

shop1,Bonn

shop2,Frankfurt

shop3,Frankfurt

shop4,Dresden

shop5.Frankfurt

Now, I want to know the number of shops in the city. I use count(DISTINCT [Shop ID]). This works fine when City is the dimension:

City, Total_no_of_shops

Bonn, 1

Frankfurt, 3

Dresden, 1

But I want to filter on the shop_ID and I want this result:

Shop_ID, Total_no_of_shops

Shop1,1

Shop2,3

Shop3,3

Shop4,1

Shop5,3

Total: 5

But get either this:

Shop1,1

Shop2,1

Shop3,1

Shop4,1

Shop5,1

Or this:

Shop1,1

Shop2,3

Shop3,-

Shop4,1

Shop5,-

I've tried total count, set analysis, even loaded another column in script (If isnull(shop_ID),'','1') and tried to calculate with sum instead of count, but at best get the last mentioned result. Could anyone please advice me how to proceed?

[swuehl · Accepted solution]

You want to count the number of shops in the same city as the location of shop in dimension line?

(Would be good if you add these requirements, otherwise it's hard to know)

Try this expression

=sum( aggr(NODISTINCT count(distinct Shop_ID), City))

or

=sum( aggr(count(total<City> DISTINCT Shop_ID), City, Shop_ID))

[edited 2nd expression]

[#3]

Hello and thanks for the reply,

yes, exactly. I want to count the number of shops in the same city as the location of shop in dimension line. The second expression works in dimension line, only the total count of shops is 11, but should be 5.

[swuehl]

=if(dimensionality(),

     sum( aggr(count(total<City> DISTINCT Shop_ID), City, Shop_ID)),

     count(distinct Shop_ID)

)

This uses count(distinct Shop_ID) for the total line (check by dimensionality() ).

[Anonymous]

Hi ,

total count you can go to chart properties -> Expression -> you see 'Total Mode'  down right corner

Select third option then 'Total count'

[#6]

Works fine, thanks!

[#7]

Thanks for the hint!