Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Total count for dimension when different dimension selected

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?

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Total count for dimension when different dimension selected

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]

View solution in original post

6 Replies
Highlighted
MVP
MVP

Re: Total count for dimension when different dimension selected

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]

View solution in original post

Highlighted
Not applicable

Re: Total count for dimension when different dimension selected

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.

Highlighted
MVP
MVP

Re: Total count for dimension when different dimension selected

=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() ).

Highlighted
Specialist II
Specialist II

Re: Total count for dimension when different dimension selected

Chartcount.pngHi ,

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

Select third option then 'Total count'

Highlighted
Not applicable

Re: Total count for dimension when different dimension selected

Works fine, thanks!

Highlighted
Not applicable

Re: Total count for dimension when different dimension selected

Thanks for the hint!