topic Re: Total count for dimension when different dimension selected in QlikView

Total count for dimension when different dimension selected

Tue, 26 May 2015 11:39:42 GMT

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?

Re: Total count for dimension when different dimension selected

swuehl — Tue, 26 May 2015 15:30:08 GMT

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))

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

[edited 2nd expression]

Re: Total count for dimension when different dimension selected

Wed, 27 May 2015 09:43:57 GMT

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.

Re: Total count for dimension when different dimension selected

swuehl — Wed, 27 May 2015 09:53:43 GMT

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

Re: Total count for dimension when different dimension selected

Anonymous — Wed, 27 May 2015 09:54:30 GMT

Hi ,

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

Select third option then 'Total count'

Re: Total count for dimension when different dimension selected

Wed, 27 May 2015 10:09:02 GMT

Works fine, thanks!

Re: Total count for dimension when different dimension selected

Wed, 27 May 2015 10:09:16 GMT

Thanks for the hint!