topic Re: Percentage in Graph - Expression problem in QlikView

Percentage in Graph - Expression problem

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a table with the following fields:-

Sales_Order_No - This holds Sales Order Numbers
Month - This holds the Month the Sales Order was created
Year - This holds the Year the Sales Order was created.
Order_On_Time - This field holds one of two values....Either 'On Time' or 'Late' and describes whether or not the Order reached the Customer on time or not.

I am trying to create a graph by month showing a percentage of 'On Time' Orders only but I am struggling to get the expression correct. The base data looks like this (Simple chart):-

I have managed to 'hide' the 'Late' Orders and see the 'On Time' totals only by using this:-

=sum(match('On Time',ORDER_ON_TIME))

If I try to divide this by the Total Orders to get the percentage, I get odd results:-

=sum(match('On Time',ORDER_ON_TIME)) / sum(ORDER_ON_TIME)

gives me nothing:-

and

=sum(match('On Time',ORDER_ON_TIME)) / sum(match('On Time',ORDER_ON_TIME)) + sum(match('Late',ORDER_ON_TIME))

gives me

I'd like to see what percentage were 'On Time' each month so I can see if our 'On Time Delivery' to the customer is improving over time.

Eg,

Jan would be 728 / (728+1941) = 0.27 = 27%

Feb would be 788 / (788 + 1812) = 0.30 = 30%

Mar would be 978 / (978 + 2154) = 0.31 = 31 %

etc

Any help would be appreciated.

Many Thanks

Jason

Re: Percentage in Graph - Expression problem

sunny_talwar — Tue, 22 Mar 2016 20:22:42 GMT

Can you try this:

Sum({<ORDER_ON_TIME = {'On Time'}>}[On Time])/Sum([On Time])

Sum({<ORDER_ON_TIME = {'On Time'}>}[On Time])/Sum({<ORDER_ON_TIME = {'Late'}>}[On Time])

Re: Percentage in Graph - Expression problem

johnw — Tue, 22 Mar 2016 20:48:07 GMT

Sum(match('On Time',ORDER_ON_TIME)) shouldn't give you a positive sum. It should give you a negative sum. The match() should return -1 if the order is on time, 0 if it isn't.

Sum(ORDER_ON_TIME) gives you nothing because you're telling it to sum a text field. You can't sum a text field and get any logical result, so it gives you null, and that null propogates to the overall expression.

Sum(match('On Time',ORDER_ON_TIME)) / sum(match('On Time',ORDER_ON_TIME)) + sum(match('Late',ORDER_ON_TIME)) is missing a set of parentheses after the / and at the end. You're doing the division first, so that part would return 1. Then the second part must for some reason be returning 0. Maybe you selected the value 'On Time', in which case there would be no matching late orders. Anyway, you could make something along those lines work, but it would be inefficient.

Set analysis is more efficient. This version is a strict percentage ignoring your selections in the ORDER_ON_TIME field:

count({<ORDER_ON_TIME={'On Time'}>} distinct Sales_Order_No)/count({<ORDER_ON_TIME=>} distinct Sales_Order_No)

This one gives you the percentage of on time orders in the currently-matching orders. So if you selected on time, you'd see 100%. If you selected late, you'd see 0%.

count({<ORDER_ON_TIME*={'On Time'}>} distinct Sales_Order_No)/count(distinct Sales_Order_No)

Another approach would be to add counter fields to your table in the script.

,if(ORDER_ON_TIME='On Time',1) as On_Time_Count
,1 as All_Count

And in your chart:

sum(On_Time_Count)/sum(All_Count)

Re: Percentage in Graph - Expression problem

Wed, 23 Mar 2016 09:10:01 GMT

Thanks for your help guys. Your comments have helped me find the solution:-

=count({<ORDER_ON_TIME={'On Time'}>} Sales_Order_No) / count(ORDER_ON_TIME)

This works perfectly. I had 'ORDER_ON_TIME' set as a Dimension too which was causing me headaches. Removing this and using the command above has solved it.

Jason