8 Replies Latest reply on Dec 20, 2016 5:59 AM by Steven Mullin

    Divide Measure by Count of Hours in the day with Measure Value > 0

    Steven Mullin

      Hey guys,

       

      I'm having a hard time working something out.  Hopefully I can explain my problem clearly.  I have attached a packaged workbook (v9.2) to help.

       

      I have a relatively simple dataset with a Date Field and several measures which give me an hourly number.  There is a calculated field which adds up each of the measures for each day.  I then have a second calculated field which divides the first by 24 to give me a daily average.  See below image -

      Capture1.JPG

      My problem is that not every day has data for every hour.  If I expand the 'Day' pill to show hourly data I'll get something like below image for Dec 16 -

      Capture2.JPG

      As you can see, the first 2 hours are at 0.  This means that, to get the average for the day, I would like to divide by 22.  All days are obviously different.  Some have data for the full 24 hours while others may only have 1.

       

      How do I go about creating a Calculation that gives me this calculated average?  I feel like I've got fairly close with 'Calculation1', but I can quite figure out the next step.  Is some form of LOD calculation required?