How to calculate monthly percentage figures across 2 columns

[Sam_Thomas]

Hi Experts, 

I am trying to create a combo chart to show a couple of different measures. I want a bar chart to show total monthly Work Orders that have been raised, with a line measure showing how many work orders each month have been 'completed', based on a different column. 

How can I calculate, and map on a chart, the total monthly completed work orders?

The 'Work Order Raised Date' shows when a work order has been raised. The 'Actual Completion Date' shows if a work order has been completed. I need to show the % of completed (where a value exists in 'Actual Completion Date') work orders.

I've tried a few different ways but nothing seems to actual appear on the graph?

Any help appreciated. 

[rbartley]

Hi,

Take a look at the attached qvd file.  I have calculated 2 measures: number of orders raised and number of orders completed and represented them on a combo chart.  I'm not sure if this is the complete solution as I don't understand your comment about % of orders complete, but if you explain how to calculate that, I can update the app.

[Sam_Thomas]

Thank you so much for your response. From the screenshot, it looks exactly how I would have imagined. 

In terms of completion percentage, I want to show Total Completed Work Orders/Total Raised Work Orders*100 for each month. 

I.e. if we raised 1000 jobs in March, and closed 900, the completion rate would be 90%. 

Unfortunately I am unable to open the QVF file and I am using the browser version of Qliksense and don't have access to the folders locally. Unless there's a way I can load it that I'm not aware of(?)

[rbartley]

If you're using Qlik Sense Enterprise, you can import it using the QMC  url   <servername>/qmc or ask your administrator to do it for you.

I've added the % completed as a new line and attached a new version of the app.  Since in some months the number of completed orders exceeds the number of raised orders, the % of completed orders exceeds 100%, as is the case in December 2020, where orders raised was 288 and orders completed 566.

[Sam_Thomas]

 I still can't load that file unfortunately.  And apparently the only person with access that can is off until Monday! 

Would it be possible to show your expressions so I can understand how you achieved the above, if you have time to share. 

Had another go this morning and I simply cannot get the calculation right or map it correctly on the chart, it just ends up in a complete mess. It's frustrating to know the answer is right there in the QVF you kindly provided and I can't access it!

Thanks again. 

[rbartley · Accepted solution]

Ok, first of all, I did most of the work in the load script:

[Sheet1]:
LOAD
	[Ellipse work Order],
	[Work Order Raised Date],
	[Actual Completion Date]
 FROM [lib://AttachedFiles/Sample Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

CountByDay:
//raised
Load
	[Work Order Raised Date] as CalendarDate,
    Sum(1) as Work_Orders_Raised
Resident [Sheet1]
Group By [Work Order Raised Date];

//completed
Join Load
	[Actual Completion Date] as CalendarDate,
    Sum(1) as Orders_Completed
Resident [Sheet1]
Group By [Actual Completion Date];

Drop Table [Sheet1];

 You can see above that I transformed the data to get the number orders raised and number of orders completed by date.

Them, in the combo chart, the dimension is:

=If(Not(IsNull(CalendarDate)),Year(CalendarDate)&'/'&Month(CalendarDate))

I use Not(IsNull()) above to cater for those orders that have not yet been completed (I probably could have filtered this out in the load script, but it was quick and dirty)

The measures are defined as:

orders raised (primary axis) :  Sum(Work_Orders_Raised)

orders completed (primary axis):   Sum(Orders_Completed)

% completed (secondary axis): Sum(Orders_Completed)*100/Sum(Work_Orders_Raised)

[Sam_Thomas]

Thank you so much for that. I have been off work for a while but have just tried this and it worked! Which has saved me a lot of stress. It's really very appreciated. 

I am having an issue with the dates not correctly formatting, though. FYI - I am using a larger data set now, including work orders from 2017-Present. 

When I follow your instructions above, everything seems to work fine, but the dimension dates on the x axis are out of order. (2017/Feb, 2017/April (Jan & March are found way up the axis)). All the sorting variations I have tried have not worked, which is strange. Would it be anything to do with the load editor or the fact I am using a larger data set?

Thanks again. 

[rbartley]

Sorry, I haven't logged in for a while.  It sounds to me as though it's probably because the dates are being sorted alphabetically (although I can't confirm it without seeing a screen print of a copy of your app).  In which case, you can use the sort by expression option and just use the date formatted to YYYYMM format instead of YYYY/MMM.