topic Re: Grouped bar chart percentage not working in QlikView

Grouped bar chart percentage not working

Shiva123 — Sat, 16 Nov 2024 03:00:41 GMT

HI,

Every one i have a bar chart with a cyclic group plus a dimension and i have only one expression which is

count ( distinct units) / count ( total distinct units ) and this expression is represented as percentage . But this

percentage is not summing unto 100 %. and the reason behind is " count ( total distinct units ) " is not calculating

properly. When i changed the bar chart to straight table to find out the issue , the denominator value should be a sum of

rows but its getting expression total . So any one can tell me on how to get sum of rows value in the denominator , i tried

aggr function , total <dimension 1, dimension 2 > both of them didn't work

Re: Grouped bar chart percentage not working

rubenmarin — Mon, 15 Jul 2019 08:36:48 GMT

Hi, in example, for this data:

LOAD * Inline [ dim1, dim2, val a,d,3 a,d,2 a,d,3 b,d,2 c,e,3 ]

This expression seems to work:

count(distinct val) / sum(TOTAL aggr(count(TOTAL <dim1, dim2> distinct val), dim1, dim2))

Re: Grouped bar chart percentage not working

Shiva123 — Mon, 15 Jul 2019 20:41:06 GMT

hi,

Rubenmarin thanks for your reply , the expression is working when there is a single dimension, but in my bar chart i have a cyclic group and i have tried to add all the dimensions that are there in the cyclic group to the total and aggr function for the denominator and it didn't work .

Thanks

Shiva

Re: Grouped bar chart percentage not working

rubenmarin — Tue, 16 Jul 2019 07:06:53 GMT

Hi Shiva, you can try with:

count(distinct val) / sum(TOTAL aggr(count(TOTAL <dim1, $(=GetCurrentField(GroupName))> distinct val), dim1, $(=GetCurrentField(GroupName))))

or:

count(distinct val) / sum(TOTAL aggr(count(TOTAL <$(=GetCurrentField(GroupName1)), $(=GetCurrentField(GroupName2))> distinct val), $(=GetCurrentField(GroupName1)), $(=GetCurrentField(GroupName2))))

You need to get only the active dimensions, not all dimensions in group

Re: Grouped bar chart percentage not working

Shiva123 — Wed, 17 Jul 2019 00:25:19 GMT

Hi Rubenmarin i tried this , it didn't work . i am defining market in my expression will that be an

issue , meaning my expression is count( DISTINCT{$<region = {'america'}>} units ) /

sum(total aggr( count( total <$(=Vcyclicgroup)>{$<region = {'america'},>} distinct

units),$(=Vcyclicgroup))) . like this i am defining for all the regions ( meaning creating one

expression for each region ).

let Vcyclicgroup = GetCurrentField(GroupName))

Thanks

Shiva

Re: Grouped bar chart percentage not working

qliksus — Wed, 17 Jul 2019 04:45:26 GMT

Can you attach your sample app which has the issue ?? for test purpose create a column 1 as flag in your load and try testing your expression

count ( distinct value) / sum(flag)

Re: Grouped bar chart percentage not working

jonathandienst — Wed, 17 Jul 2019 05:43:12 GMT

The value returned with a DISTINCT will be different for different dimensionality, so the expression:

sum(distinct value) / sum(total distinct value)

will not normally add up to 100% -- it would normally be more than 100%. This is because values that exist in more than one dimension are counted multiple times in the sum(), but only once in the sum(total).

Re: Grouped bar chart percentage not working

rubenmarin — Wed, 17 Jul 2019 06:34:16 GMT

Hi, what means 'it didn't work'? no result or no 100% result?

If not 100% can be ok, as Jonathan says doing a distinct count can count the same value many times in numerator but only one time in denominator.

If no result at all maybe it's beacause the extra parenthesys in Vcyclicgroup, or becasue you have fixed 'GroupName' when this should be the name of the cyclic group. And better use set instead of let when declaring strings that will be used as expressions:

Set Vcyclicgroup = GetCurrentField(YourRealGroupName);

If field name has blank spaces or any other special character you need to add brackets:

total <$([=Vcyclicgroup])>{$<region = {'america'},>}

The comma after 'america' also can cause an error, it should be removed.