Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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]
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]
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.
=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() ).
Hi ,
total count you can go to chart properties -> Expression -> you see 'Total Mode' down right corner
Select third option then 'Total count'
Works fine, thanks!
Thanks for the hint!