3 Replies Latest reply on Oct 19, 2016 8:43 AM by David Li

    Need calculated field to run at the "day" level only, even when my date view is set to weekly.

    Michael Brents

      Hello Everyone,

       

      This is my first time posting a question in the community, so if I break any type of "protocol" my apologies upfront. 

       

      I have created a calculated field which will ascribe numeric "point" values based a mileage thresholds.  If 60-90 miles, 1 point, 90-120 miles, 1.5 points, etc.  My calculation is below.  The problem is, that these rules only apply to workers on a "daily" basis, and not as miles add up "per week."  However, I need to maintain a "per week" view on my dashboard, but for this calculated field to consider mileage driven "per day" and not per "week."  I've tried to use a FIXED calculated field to specify at the Date field level, but that hasn't worked.  Any help appreciated!!!

       

      IF [Visit Calculated Miles  ]>=0 AND [Visit Calculated Miles  ]<60 THEN 0

      ELSEIF [Visit Calculated Miles  ]>=60 AND [Visit Calculated Miles  ]<90 THEN 1

      ELSEIF [Visit Calculated Miles  ]>=90 AND [Visit Calculated Miles  ]<120 THEN 1.5

      ELSEIF [Visit Calculated Miles  ]>=120 AND [Visit Calculated Miles  ]<150 THEN 2

      ELSEIF [Visit Calculated Miles  ]>=150 AND [Visit Calculated Miles  ]<180 THEN 2.5

      ELSEIF [Visit Calculated Miles  ]>=180 AND [Visit Calculated Miles  ]<210 THEN 3

      ELSEIF [Visit Calculated Miles  ]>=210 AND [Visit Calculated Miles  ]< 240 THEN 3.5

      ELSEIF [Visit Calculated Miles  ]>= 240 AND [Visit Calculated Miles  ]<270 THEN 4

      ELSEIF [Visit Calculated Miles  ]>=270 AND [Visit Calculated Miles  ]<300 THEN 4.5

      ELSEIF [Visit Calculated Miles  ]>=300 AND [Visit Calculated Miles  ]<330 THEN 5 ELSE NULL END

        • 1. Re: Need calculated field to run at the "day" level only, even when my date view is set to weekly.
          David Li

          Hi Michael! You can do this by putting SUM() around all the field names, like this:

          IF SUM([Visit Calculated Miles  ])>=0 AND SUM([Visit Calculated Miles  ])<60 THEN 0

          This switches the calc to an aggregation calc instead of a row-level one.

           

          On another note, you could probably simplify this calculation considerably since your buckets just increase by 30. Perhaps you could divide by 30, round down, and then multiply that by 0.5. For instance:

          FLOOR(SUM([Visit Calculated Miles ])/30) * 0.5

          • 2. Re: Need calculated field to run at the "day" level only, even when my date view is set to weekly.
            Michael Brents

            Hello David!

             

            Thank you for such an expedient response.  I should have mentioned in my original post that the Visit Calculated Miles  ] measure was already an aggregated calculation which is SUM([Visit Calculated Miles])/COUNTD([Branch Group]).  So I couldn't put SUM's in front of that measure as it was already aggregated.

             

            Perhaps some examples of my data will help to demonstrate better.   Below you can see what my calculation does when data is viewed at the "daily" level.  It works great.  However, below that I've included what it looks like if I view by week, then miles get added for the whole week, and not calculated day by day and then summed for the week after that.  How can I get results that are calculated at a "daily" level, but then viewed and summed appropriately at the "weekly" level?

             

              

            Day of Date - Visit And Non-Visit ActivityMileage Bonus Points
            19-May-161.5
            20-May-161.5
            23-May-161
            24-May-160
            25-May-161.5
            26-May-160
            27-May-160
            29-May-161.5
            30-May-161.5
            31-May-161
            1-Jun-161.5
            2-Jun-160
            13-Jun-160
            14-Jun-16

            1

             

             

             

            versus when my view changes to (Week Beginning by)

             

              

            Week of Date - Visit And Non-Visit ActivityMileage Bonus Points
            15-May-16                             null
            22-May-16                                5
            29-May-16                             null
            12-Jun-16                             null
            19-Jun-164.5
            26-Jun-164.5
            3-Jul-165
            10-Jul-165
            • 3. Re: Need calculated field to run at the "day" level only, even when my date view is set to weekly.
              David Li

              Ah, I see. Can you share a packaged workbook so I can see all the calcs and how the data is laid out?