topic Re: showing incorrect count in QlikView

showing incorrect count

arulsettu — Wed, 25 Nov 2020 16:16:04 GMT

hi i am doing simple count

count(id) and for ratio count(id)/count(total id)

count(total Id) is incorrect.

in straight table i checked.in properties expression tab if i use Expression as total it showing correct.

how to do this bar chart.

Note: i am using cyclic group in dimension

Re: showing incorrect count

MK_QSL — Thu, 03 Mar 2016 08:59:19 GMT

Provide sample data !

Re: showing incorrect count

MK_QSL — Thu, 03 Mar 2016 09:03:46 GMT

Can you tell us where you think it is wrong? What is the exact result according to your need?

Re: showing incorrect count

arulsettu — Thu, 03 Mar 2016 09:06:47 GMT

please check the count total. all vech count

Re: showing incorrect count

arulsettu — Thu, 03 Mar 2016 09:11:04 GMT

total should be 31057 for year 2016

Re: showing incorrect count

MK_QSL — Thu, 03 Mar 2016 09:14:36 GMT

How 31057?

Re: showing incorrect count

t_chetirbok — Thu, 03 Mar 2016 09:16:41 GMT

Why?

total count is 43471

Re: showing incorrect count

jagan — Thu, 03 Mar 2016 09:17:40 GMT

Hi,

43417 is correct value. Not sure do you missed any filters in the expression or script?

Regards,

jagan.

Re: showing incorrect count

arulsettu — Thu, 03 Mar 2016 09:21:06 GMT

Hi Jagan,

No filters applied only year 2016. please manually count the 10 vech. you will get 31057

Re: showing incorrect count

t_chetirbok — Thu, 03 Mar 2016 09:22:37 GMT

So, in your bar chart 2 expression with different format, for first axes should be 0-10000, for second 0 %- 100%.

Maybe second expression can be Text as Pop-Up for example

Re: showing incorrect count

Anonymous — Thu, 03 Mar 2016 09:23:29 GMT

On the Chart's Dimension Limits tab it has a limit set to only show the Largest 10 values. If you tick Show Others then the Total shown with match the sum of the numbers below.

Re: showing incorrect count

arulsettu — Thu, 03 Mar 2016 09:25:50 GMT

hi Bill i need to show Top 10 vech

orginal code is

If(Rank(count({<BW_FM_DT_YEAR={$(=max(BW_FM_DT_YEAR))}>}BW_VEH_ID)) <= 10,Count({<BW_FM_DT_YEAR={$(=max(BW_FM_DT_YEAR))}>}BW_VEH_ID))

to find ratio i did like this

If(Rank(count({<BW_FM_DT_YEAR={$(=max(BW_FM_DT_YEAR))}>}BW_VEH_ID)) <= 10,Count({<BW_FM_DT_YEAR={$(=max(BW_FM_DT_YEAR))}>}Total BW_VEH_ID))

Re: showing incorrect count

jagan — Thu, 03 Mar 2016 09:28:40 GMT

Hi,

Total will take all the values irrespective of dimension limits.

Re: showing incorrect count

jagan — Thu, 03 Mar 2016 09:29:19 GMT

Check this file.

Regards,

jagan.

Re: showing incorrect count

Anonymous — Thu, 03 Mar 2016 09:30:58 GMT

Your Set Analysis is warping the total. Try removing it :

If(Rank(count(BW_VEH_ID)) <= 10,Count(BW_VEH_ID))

Re: showing incorrect count

arulsettu — Thu, 03 Mar 2016 09:32:20 GMT

hi Jagan

result shold be like this in bar chart

Make	Count	ratio
Honda	3153	10%
BMW	1360	4%
Mercedes	1657	5%
Mitsubishi	4900	16%
Kia	898	3%
Toyota	8785	28%
Nissan	6753	22%
Hyundai	860	3%
Chevrolet	1606	5%
GMC	1085	3%
Totals	31057

Re: showing incorrect count

Anonymous — Thu, 03 Mar 2016 09:36:36 GMT

... or maybe more efficient with this Set Analysis :

Count({<BW_MAKE_DESC = {"=rank(Count(BW_VEH_ID))<= 10"}>} BW_VEH_ID)

Re: showing incorrect count

MK_QSL — Thu, 03 Mar 2016 09:36:54 GMT

Use below

Straight Table

Dimension

BW_MAKE_DESC

Expressions

COUNT({<BW_MAKE_DESC = {"=Rank(COUNT(BW_VEH_ID))<=10"}>}BW_VEH_ID)

Total Mode Select Sum of Rows

COUNT({<BW_MAKE_DESC = {"=Rank(COUNT(BW_VEH_ID))<=10"}>}BW_VEH_ID)/COUNT(TOTAL{<BW_MAKE_DESC = {"=Rank(COUNT(BW_VEH_ID))<=10"}>}BW_VEH_ID)

Re: showing incorrect count

t_chetirbok — Thu, 03 Mar 2016 09:39:54 GMT

for ratio use expression

If(Rank(count(BW_VEH_ID), 4, 1) <= 10,

count(BW_VEH_ID)

/sum(TOTAL aggr(If(Rank(count(BW_VEH_ID), 4, 1) <= 10,count(BW_VEH_ID)) ,BW_MAKE_DESC) )

)

Re: showing incorrect count

sfatoux72 — Thu, 03 Mar 2016 09:41:12 GMT

Change this setting for your expression and you will have what you want