Showing percentage of items with / without a value

[slong]

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 

[Taoufiq_Zarra · Accepted solution]

@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 :

[slong]

@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 🙂

[slong]

@Taoufiq_Zarra 

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

[JustinDallas]

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.

[manoranjan_d]

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 %