Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Calculate percentage of occupation of the day

Assuming I have the following table:

activity_idDateactivity_typetime periodPercentage of occupation
12018-01-168whole day 100
22018-01-17610-1425
32018-01-18610-1425
42018-01-18814-1825
52018-01-19710-1425
62018-01-19614-1825

I have to calculate how much percentage did the activity_type = 6 occupy for all the different dates with the following condition:
If there's a day in which only activities of type 6 happened, the calculated percentage should be 100%
Case in point the expected result should be 37.5%( on date 2018-01-17 there's only a single type of activity, so the calculated percentage is 100%; 100% + 25% from activity_id = 3 + 25% from activity_id = 6 = 150% divided by the number of days: 4)

25 Replies
sunny_talwar

Which date range are you getting to get?

Anonymous
Not applicable
Author

The expression doesn't seem to work. I've attached some mockup data along with the result I'm expecting from the expression. Those are all the activities for a single employee

sunny_talwar

I am not sure I follow the sample you have provided... would you be able to elaborate?

Anonymous
Not applicable
Author

I'm doing a sum of all the percentages of occupation for activity_type = 6 that are in different time periods on the same date and then make a total of those sums. If, for a day, there's only activities of type=6 then the sum value for the day becomes 1.

sunny_talwar

So, what exactly are you hoping to see and where? in a text box object? Straight table or pivot table?

Anonymous
Not applicable
Author

Expression in a pivot table. I want the total that I mentioned in an expression in a pivot table

sunny_talwar

How is this linked with

Capture.PNG

I apologize if this is something straight forward, but I am completely lost as to what exactly you are trying to do.

Anonymous
Not applicable
Author

I thought the reason why I get a sum greater than expected was due to not properly filtering the activity dates, but it wasn't that the problem.
As to what I'm trying to do, I have a pivot table with multiple employees, and I need the percentage of occupation of the worked days for activity_type = 6, as an expression.
I'm sorry if I'm rambling, I've been struggling with this pivot table for some days and frustration is setting in.

sunny_talwar

Is MockUp Excel you have attached is your expected output?

Anonymous
Not applicable
Author

The value from cell F2 is the expected output of the expression.