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

    Calculation of Average Rate and Day Packages with hours

    saikaruna.nimmala
    saikaruna.nimmala Jan 18, 2018 12:17 AM

      Hello Everyone,

       

      Could someone please help me with this?

       

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

       

       

         Ex:

      Date FilterNov 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

      • 65 Views
      • Categories: Calculations
      • Tags:
        • 1. Re: Calculation of Average Rate and Day Packages with hours
          Zhouyi Zhang
          Zhouyi Zhang Ambassador Jan 18, 2018 4:38 AM (in response to saikaruna.nimmala)

          Hi, Saikaruna

           

          Please find my solution attached.

           

          Hope this could help

           

          ZZ

          • 2. Re: Calculation of Average Rate and Day Packages with hours
            Norbert Maijoor
            Norbert Maijoor Ambassador Jan 18, 2018 5:16 AM (in response to saikaruna.nimmala)

            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