topic Re: Showing percentage of items with / without a value in App Development

Showing percentage of items with / without a value

slong — Sat, 16 Nov 2024 00:36:44 GMT

So I've extracted a relevant set of Jira data using the Jira connector. In a simplified form the data I am interested in looks like this.

What I want to end up with is a bar chart where I can see the percentage of items of type Story with and without a value in the column field_custom_field_10024.

So I would like to see something like this in the end but I'm unsure how to get it looking exactly like this from the data I have.

Many thanks in

Re: Showing percentage of items with / without a value

Taoufiq_Zarra — Mon, 15 Feb 2021 14:06:12 GMT

@slong for exemple from your data :

I can use :

Dimension :

=Valuelist('Stories with a value','Stories without a value')

Measure:

PICK(MATCH(VALUELIST('Stories with a value','Stories without a value'),'Stories with a value','Stories without a value'), count({<field_issue_type_name={'Story'},field_customfield_10024={"=len(trim(field_customfield_10024))>0"}>} field_issue_type_name)/(count({<field_issue_type_name={'Story'},field_customfield_10024={"=len(trim(field_customfield_10024))>0"}>} field_issue_type_name)+count({<field_issue_type_name={'Story'},field_customfield_10024={"=len(trim(field_customfield_10024))=0"}>} field_issue_type_name)), count({<field_issue_type_name={'Story'},field_customfield_10024={"=len(trim(field_customfield_10024))=0"}>} field_issue_type_name)/(count({<field_issue_type_name={'Story'},field_customfield_10024={"=len(trim(field_customfield_10024))>0"}>} field_issue_type_name)+count({<field_issue_type_name={'Story'},field_customfield_10024={"=len(trim(field_customfield_10024))=0"}>} field_issue_type_name)))

you can add after color and number format to %

the output from this is :

Re: Showing percentage of items with / without a value

slong — Mon, 15 Feb 2021 16:24:20 GMT

@Taoufiq_Zarra Thank you so much. That is fantastic and works perfectly. I'm new to Qlik so I'm just digesting your response to understand it fully 🙂

Re: Showing percentage of items with / without a value

slong — Mon, 15 Feb 2021 16:58:10 GMT

@Taoufiq_Zarra

This is your solution working for me. What's the easiest way to get it displaying in percentages ?

Re: Showing percentage of items with / without a value

JustinDallas — Mon, 15 Feb 2021 19:02:17 GMT

I know you've already got an answer, but you might be able to get away with less set analysis by doing some data model work.

Simply make a flag somewhere in your data load.

LOAD *, If( Len(Trim(field_customfield_10024)) > 0, 'Y', 'N' ) AS 'Has Story Value', ... ;

I think using such a flag will make your set analysis easier and more readable when you are asked to make a change 6 months from now.

Re: Showing percentage of items with / without a value

manoranjan_d — Mon, 15 Feb 2021 20:00:33 GMT

Hi,

In the script first try to create flag, by adding below script line

if(len(field_custom_10024)=0,'Y','N') as flag

now in the bar chart give the value list which will create synthetic dimension

dim:

=ValueList('Story with value', 'Story without value')

exp:

=if(ValueList('Story with value', 'Story without value')='Story with value',
(Count({<flag={'N'}>}flag)/count(TOTAL(flag))),
(Count({<flag={'Y'}>}flag)/count(TOTAL(flag))))*100 & '%'

total is the function to calculate the percentage which will disregard the dimension .

What's the easiest way to get it displaying in percentages ?

to get percentage in chart multiple by 100 and concatenate with %