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]

How are you getting 0.75 for 2018-02-15?

[Anonymous]

0.25(from Quarter of day) + 0.5(from 14-18)

[sunny_talwar · Accepted solution]

Try this

=Sum(Aggr(If(RowNo() = 1, Sum({<activity_type = {'6'}, [date(inizio)] = {"=Count(DISTINCT activity_type) > 1"}>} [Percentage of occupation])), [date(inizio)], [time period], activity_id)) +

Sum(Aggr(If(RowNo() = 1, Sum({<activity_type = {'6'}, [date(inizio)] = {"=Count(DISTINCT activity_type) = 1"}>} 1)), [date(inizio)], activity_id))

[Anonymous]

It seems to work.
Could you explain to me how does [date(inizio)] = {"=Count(DISTINCT activity_type) > 1"} work?

[sunny_talwar]

This part just say that pick dates where Count of distinct_activity is greater than 1

[Anonymous]

thanks