Hard coding values based on the calculation in stacked bar graph

[osse_aam]

Hello Qlik fam!

I am trying to hard code the values in measure for a bar graph if the percentages are too low.

My expression:

if(Valuelist('Ever-Enrolled','Audit')='Ever-Enrolled' and
(count({<Type={'Current'},[School Year]={'2020-2021'}>}distinct USI)/
count(total {<Type={'Current'},[Race/Ethnicity]=,[School Year]={'2020-2021'}>}distinct USI))<0.001,num(0.001,'<0.0%'),

if(Valuelist('Ever-Enrolled','Audit')='Ever-Enrolled' and
(count({<Type={'Current'},[School Year]={'2020-2021'}>}distinct USI)/
count(total {<Type={'Current'},[Race/Ethnicity]=,[School Year]={'2020-2021'}>}distinct USI))>=0.001,

(count({<Type={'Current'},[School Year]={'2020-2021'}>}distinct USI)/
count(total {<Type={'Current'},[Race/Ethnicity]=,[School Year]={'2020-2021'}>}distinct USI)),

if(Valuelist('Ever-Enrolled','Audit')='Audit' and
(count({<Category={'Historic'},[School Year]={'2020-2021'}>}distinct USI)/
count(total {<Category={'Historic'},[Race/Ethnicity]=,[School Year]={'2020-2021'}>}distinct USI))<0.001,num(0.001,'<0.0%'),

if(Valuelist('Ever-Enrolled','Audit')='Audit' and
(count({<Category={'Historic'},[School Year]={'2020-2021'}>}distinct USI)/
count(total {<Category={'Historic'},[Race/Ethnicity]=,[School Year]={'2020-2021'}>}distinct USI))>=0.001,

(count({<Category={'Historic'},[School Year]={'2020-2021'}>}distinct USI)/
count(total {<Category={'Historic'},[Race/Ethnicity]=,[School Year]={'2020-2021'}>}distinct USI))

))))

The highlighted part above from my expression is where I am trying to use multiple options but nothing really worked out. The above expression is giving '0.10%' but my goal is to get '<0.1%'. I am able to implement the logic without any problem in grouped bar chart but the stacked bar chart is not giving me the expected result.

Any help regarding this would be appreciated!

Thanks,

Sai.

[rubenmarin]

Hi, I tried and it shows <0.1% to me. I only achiveved to show 0.10% when I set the number format as 12,34% instead of the default 'Automatic', so maybe at some point there is something fixng the format for 2 decimals and percentage instead of use the expression format.

I also tried with stacked chart

[Vegar]

You could try to replace  num(0.001,'<0.0%') with Dual('<0.0%',0.001)

[osse_aam]

@rubenmarin  - This is what I see when I use 'Auto' format and select the specific category. It is showing as '1m' and I only see '<0.10%' when I export the data to excel but not when I hover.

I am trying to achieve the following visualization format

Thanks,

Sai.

[osse_aam]

@Vegar  - Tried Dual('<0.1%',0.001) and it's giving me '1m' too like the other expression and I only see '<0.1%' when I convert to 'view data' mode or export the chart to excel. Not sure why I don't see the same value when I hover.

Tried playing with all 'Number formatting' options available but no luck yet.  'Custom' (Number formatting) and used the pattern '<0.01%' but this is changing all the other segments to '<xx.xx%' -which is not expected.

Would have been nicer if they had expression capability for 'Format Pattern' too.

Thanks,

Sai.

[rubenmarin]

I tried in an horizontal stacked bar and it shows ok to me.

Can you upload a dummy qvf that replicates the issue so we can check and try there?

Or maybe you just need to add the format the the other expressions:

Num((count({<Category={'Historic'},[School Year]={'2020-2021'}>}distinct USI)/
count(total {<Category={'Historic'},[Race/Ethnicity]=,[School Year]={'2020-2021'}>}distinct USI)),'0.00%')

[osse_aam]

@rubenmarin - Thank you so much for your efforts!

Here is the app replicating the part of the logic with the dummy data.

[rubenmarin · Accepted solution]

Hi, I tested and in the same expression only can have a format, if all number format are '0.0%' or '<0.0%' it works, but with a mix it shows the default format 0.0. This doesn't works on visual charts but in tables a mix of formats can work (I added the same char tconverted to table to check how it works).

Another things to take care:

Note that when converting to percentage 1=100%, so 0.1=10%, and 0.1%=0.001, so to show '<0.1%' you need anumber like 0.001 and compared to number like 0.3 or 0.5 it will be too little to show in a bar.

In example, category C, it has a 4 over 10, that's a 40% not a 0.4%.

The bar wil always have a length of the number part, and to show '<10%' you'll need something like: Num(0.1,'<0.0%'),  and this will set the lenght of the bar as it was 10%, so making an higher bar than it really is.

Using values like '<0.1%' (or <10%) can be done when creating buckets, like counting how many categories or subcategories have less than 0.1%.