2 Replies Latest reply on Jan 18, 2018 5:16 AM by Norbert Maijoor

# Calculation of Average Rate and Day Packages with hours

Hello Everyone,

The requirement is: When a user selects a month, the following  should be displayed in the form of dashboard shown as below:

Ex:

 Date Filter Nov 1-30, 2017 dayof week Average Rate Monday 100 Tuesday 200 Sunday 300

Formulas:

Rate = countd(id)

I need to calculate rate of Sunday package, Monday package, Tuesday package

i.e.  I need to calculate countd(id) of Sunday package, countd(id) of Monday  package and countd(id) of Tuesdaypackage

Sunday Package  ---   Sunday 1pm-5pm  which is   eventhour  Sunday 13,14,15,16  and

Saturday 10pm-2am      Which is Saturday 10pm-11:59pm & Sunday 0-1am  which is eventhour Saturday 22,23 & Sunday 0,1 and

Friday 12am-2am     Which is all of Friday and Saturday 0-1am   which is eventhour Friday 0-23 and Saturday 0,1

Monday package--  Monday 1pm-5pm  which is   eventhour Monday 13,14,15,16  and

Sunday 10pm-2am      Which is Sunday 10pm-11:59pm & Monday0-1am which is eventhour Sunday 22,23 & Monday 0,1 and

Saturday 12am-2am     Which is all of Saturday and Sunday 0-1am   which is eventhour Saturday 0-23  and Sunday 0,1

Tuesday Package -- Tuesday 1pm-5pm  which is eventhour  Tuesday 13,14,15,16  and

Monday 10pm-2am      Which is Monday 10pm-11:59pm & Tuesday 0-1am  which is eventhour Monday 22,23 & Tuesday 0,1 and

Sunday 12am-2am     Which is all of Sunday and Monday 0-1am   which is eventhour Sunday0-23 and Monday 0,1

After calculating all the above, I need to calculate Average Rate

Average Rate = countd(id)/ no of Sundays, mondays, tuesdays in the week .

i.e. if the user selects Nov, 2017 then since no. of sundays in nov, 2017 is 4, the average rate is countd(id)/4

• ###### 1. Re: Calculation of Average Rate and Day Packages with hours

Hi, Saikaruna

Hope this could help

ZZ

• ###### 2. Re: Calculation of Average Rate and Day Packages with hours

Hi Saikaruna,

Not Sure but find my approach in addition to directions provided by Zhouyi Zhang stored in attached workbook version 10.3 located in the original thread.

1. D1. Timeslots: if [Eventhour]>=13 and [Eventhour]<17 then "1pm-5pm"

elseif [Eventhour]>=22 and [Eventhour]<24 then "10pm-0am"

elseif [Eventhour]>=0 and [Eventhour]<2 then "0am-2am"

elseif [Eventhour]>=0 and [Eventhour]<1 then "0am-1am"

END

2. D2. Eventdate Weekdays:

3. D3. Slots:

if ([D2. Eventdate Weekdays]=1 and [D1. Timeslots]="1pm-5pm")

or  ([D2. Eventdate Weekdays]=7 and [D1. Timeslots]="10pm-0am")

or  ([D2. Eventdate Weekdays]=1 and [D1. Timeslots]="0am-2am")

or  ([D2. Eventdate Weekdays]=6)

or  ([D2. Eventdate Weekdays]=7) and [D1. Timeslots]="0am-2am" then "1. Sunday Package"

elseif ([D2. Eventdate Weekdays]=2 and [D1. Timeslots]="1pm-5pm")

or  ([D2. Eventdate Weekdays]=1 and [D1. Timeslots]="10pm-0am")

or  ([D2. Eventdate Weekdays]=2 and [D1. Timeslots]="0am-2am")

or  ([D2. Eventdate Weekdays]=7)

or  ([D2. Eventdate Weekdays]=1) and [D1. Timeslots]="0am-2am" then "2. Monday Package"

elseif ([D2. Eventdate Weekdays]=3 and [D1. Timeslots]="1pm-5pm")

or  ([D2. Eventdate Weekdays]=2 and [D1. Timeslots]="10pm-0am")

or  ([D2. Eventdate Weekdays]=3 and [D1. Timeslots]="0am-2am")

or  ([D2. Eventdate Weekdays]=1)

or  ([D2. Eventdate Weekdays]=2) and [D1. Timeslots]="0am-2am" then "3. Tuesday Package"

elseif ([D2. Eventdate Weekdays]=4 and [D1. Timeslots]="1pm-5pm")

or  ([D2. Eventdate Weekdays]=3 and [D1. Timeslots]="10pm-0am")

or  ([D2. Eventdate Weekdays]=4 and [D1. Timeslots]="0am-2am")

or  ([D2. Eventdate Weekdays]=2)

or  ([D2. Eventdate Weekdays]=3) and [D1. Timeslots]="0am-2am" then "4. Wednesday Package"

elseif ([D2. Eventdate Weekdays]=5 and [D1. Timeslots]="1pm-5pm")

or  ([D2. Eventdate Weekdays]=4 and [D1. Timeslots]="10pm-0am")

or  ([D2. Eventdate Weekdays]=5 and [D1. Timeslots]="0am-2am")

or  ([D2. Eventdate Weekdays]=3)

or  ([D2. Eventdate Weekdays]=4) and [D1. Timeslots]="0am-2am" then "5. Thursday Package"

elseif ([D2. Eventdate Weekdays]=6 and [D1. Timeslots]="1pm-5pm")

or  ([D2. Eventdate Weekdays]=5 and [D1. Timeslots]="10pm-0am")

or  ([D2. Eventdate Weekdays]=6 and [D1. Timeslots]="0am-2am")

or  ([D2. Eventdate Weekdays]=4)

or  ([D2. Eventdate Weekdays]=5) and [D1. Timeslots]="0am-2am" then "6. Friday Package"

elseif ([D2. Eventdate Weekdays]=7 and [D1. Timeslots]="1pm-5pm")

or  ([D2. Eventdate Weekdays]=6 and [D1. Timeslots]="10pm-0am")

or  ([D2. Eventdate Weekdays]=7 and [D1. Timeslots]="0am-2am")

or  ([D2. Eventdate Weekdays]=5)

or  ([D2. Eventdate Weekdays]=6) and [D1. Timeslots]="0am-2am" then "7. Saturday Package" END

4. M1. Count days: {include [D2. Eventdate Weekdays]:countd([D4. Eventdate (Month / Day / Year)])}

5. M2. Count ID: count([Id])

6. M3. Rate: [M2. Count ID]/sum([M1. Count days])

7. Drag the required objects to the indicated locations & show filter eventdate

Regards,

Norbert