Calculate percentage of occupation of the day

[Anonymous]

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)

[sunny_talwar]

May be this

Sum({<activity_type = {'6'}>} [Percentage of occupation])/Count(DISTINCT Date)

[Anonymous]

normally this would work, but how should I tackle the case in which a single day has only activities of activity_type 2? Case in point, for activity_id = 2, because it's the only activity for that day, the value should be 100 when I calculate the expression

[sunny_talwar]

You mean activity 6, right?... checking

[Anonymous]

activity 6 is on the same day as activity 5, so its percentage of occupation should be unchanged

[sunny_talwar]

Try this

=(Sum({<activity_type = {'6'}, Date = {"=Count(DISTINCT activity_type) > 1"}>} [Percentage of occupation]) + Sum({<activity_type = {'6'}, Date = {"=Count(DISTINCT activity_type) = 1"}>} 100))/Count(DISTINCT Date)

[sunny_talwar]

My bad, I got confused between activity and activity_type... I thought you were talking about activity_type...

[Anonymous]

Almost there. How can I add a filter to take only the activities after a certain Date in a set expression?

[sunny_talwar]

May be like this

=(Sum({<activity_type = {'6'}, Date = {"=Count(DISTINCT activity_type) > 1"}*{YourDateRange}>} [Percentage of occupation]) + Sum({<activity_type = {'6'}, Date = {"=Count(DISTINCT activity_type) = 1"}*{YourDateRange}>} 100))/Count({<Date = {YourDateRange}>} DISTINCT Date)

[Anonymous]

Could you give me an example for how to use {YourDateRange}?