Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Assuming I have the following table:
activity_id | Date | activity_type | time period | Percentage of occupation |
---|---|---|---|---|
1 | 2018-01-16 | 8 | whole day | 100 |
2 | 2018-01-17 | 6 | 10-14 | 25 |
3 | 2018-01-18 | 6 | 10-14 | 25 |
4 | 2018-01-18 | 8 | 14-18 | 25 |
5 | 2018-01-19 | 7 | 10-14 | 25 |
6 | 2018-01-19 | 6 | 14-18 | 25 |
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)
Which date range are you getting to get?
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
I am not sure I follow the sample you have provided... would you be able to elaborate?
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.
So, what exactly are you hoping to see and where? in a text box object? Straight table or pivot table?
Expression in a pivot table. I want the total that I mentioned in an expression in a pivot table
How is this linked with
I apologize if this is something straight forward, but I am completely lost as to what exactly you are trying to do.
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.
Is MockUp Excel you have attached is your expected output?
The value from cell F2 is the expected output of the expression.