Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Not applicable

Calculated percentage on specific date in field

Hi,

I have the below expression which calculates the percentage of calls where the withinfix field is No by doing a count of the field callref.

=num(count({$<[withinfix]={No}>} withinfix)  / count(callref), '##.00%')

However I want to filter it futher by picking up the above only if the location field = 'CLO'.  How would I do this?

Regards,

Jon

1 Solution

Accepted Solutions
Highlighted
Not applicable

Re: Calculated percentage on specific date in field

Hi Jonathan, Please try like below:

NUM( count({$<withinfix={No}, location={'CLO'}>} withinfix)  / count({<location={'CLO'}>}callref) , '##0.00%' )

In the above expression, the Num function should be return the % Value. If not please calculate the % by multiplying with 100.

NUM( count({$<withinfix={No}, location={'CLO'}>} withinfix)  / count({<location={'CLO'}>}callref) , '##0.0000' ) *100 & '%'


View solution in original post

6 Replies
Highlighted
mov
Esteemed Contributor III

Re: Calculated percentage on specific date in field

Jon, try this:

=num(count({$<[withinfix]={No}, location={'CLO'}>} withinfix)  / count(callref), '##.00%')

Highlighted
Not applicable

Re: Calculated percentage on specific date in field

Hi,

I forgot to mention that my callref field pulls out all the calls references in the database, so I have to run the below so my callref field only pulls out a location of CLO.

num(count({$<[withinfix]={No}, location={'CLO'}>} withinfix)  / count(if(location = 'CLO', callref)))

If I run the above script, it pull through the following: 0.13337, which is correct but how would I display this as a percentage because if I add in the '##.00%', it pull up all my callref which is 68000 instead of my CLO call ref which is 1522.

Highlighted
mov
Esteemed Contributor III

Re: Calculated percentage on specific date in field

First, it is better to be consistent - if you use set analysis in the numerator, use it in denominator as well. 
count({$<withinfix={No}, location={'CLO'}>} withinfix)  / count({<location={'CLO'}>}callref)

Or use "if" in both:
count(if(withinfix='No' and location='CLO', withinfix))  / count(if(location='CLO', callref))

Next, about formatting.
If you use this expression within a chart, it is better to define format in the chart properties, tab "Number".  No need to define format in expression itself.
If your expression is outside of the chart, your formatting should work.  Check the syntax, especially the parenthesis.

Highlighted
Not applicable

Re: Calculated percentage on specific date in field

Hi Michael,

Thanks for the response back, I am using the expression in a text object.

Highlighted
Not applicable

Re: Calculated percentage on specific date in field

Hi Jonathan, Please try like below:

NUM( count({$<withinfix={No}, location={'CLO'}>} withinfix)  / count({<location={'CLO'}>}callref) , '##0.00%' )

In the above expression, the Num function should be return the % Value. If not please calculate the % by multiplying with 100.

NUM( count({$<withinfix={No}, location={'CLO'}>} withinfix)  / count({<location={'CLO'}>}callref) , '##0.0000' ) *100 & '%'


View solution in original post

Highlighted
Not applicable

Re: Calculated percentage on specific date in field

Thank you to both of you for your help.