Grouped bar chart percentage not working

[Shiva123]

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 

[rubenmarin]

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

[Shiva123]

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 

[rubenmarin · Accepted solution]

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

[Shiva123]

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  

[qliksus]

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)

[jonathandienst]

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

[rubenmarin]

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.