Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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

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

Capture.PNG

Anonymous
Not applicable
Author

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

sunny_talwar

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))

Capture.PNG

Anonymous
Not applicable
Author

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
Not applicable
Author

thanks