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

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 -

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 -

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?

• ###### 1. Re: Divide Measure by Count of Hours in the day with Measure Value > 0

Hi Steven,

Find my approach as reference below and stored in attached workbook version 9.3

a. {Fixed [Datetime (Days)]: [Daily Total]}

b. Entry yes/no: if {fixed [Datetime]:[Daily Total]}=0 then 0 else 1 end

c. Entry fixed: {Fixed [Datetime (Days)]: sum([entry yes/no])}

d. Average per day: [Daily Total fixed]/[entry yes/no]

1 of 1 people found this helpful
• ###### 2. Re: Divide Measure by Count of Hours in the day with Measure Value > 0

Hi Norbert,

Thanks for the reply.  I'm just going to spend some time going through this before I mark down as answered.  I haven't used LoD calculations before and want to make sure I understand exactly what's going on.

• ###### 3. Re: Divide Measure by Count of Hours in the day with Measure Value > 0

Hi Steven,

Thanks for prompt reply: Please take your time. You can find more info on LOD here

• ###### 4. Re: Divide Measure by Count of Hours in the day with Measure Value > 0

Hi again,

This is an excellent answer.  Thank you very much.  I knew it was possible to do what I wanted, I was just at my wit's end.

Could you explain why it is necessary to have both the 'Entry Fixed' and the 'Entry Yes/No' fields?  They both appear to show the same data.

Also, great to have a working example of LoD Calcs.  This should help me in the future too.

• ###### 5. Re: Divide Measure by Count of Hours in the day with Measure Value > 0

Hi Steven,

"Entry fixed" and "Entry Yes/No" shows very nice where LOD "kicks in". I "fixed" "Entry fixed" based on LOD functionality on day level .

Below i dropped the initial date notation at hour level into the sheet and Entry yes/no was on "day-level" and now comes down on hour-level. So depending on which dimensions are brought in and the usages of LOD defined measures the data will be the same or will adjusted.

Hope I clarified sufficiently....

• ###### 6. Re: Divide Measure by Count of Hours in the day with Measure Value > 0

Yes, that makes perfect sense.

Just one more question.  How did you generate the DateTime (Days) field?  I clicked on edit and it popped up a custom date box, but I have no idea how it got that way to being with.

• ###### 7. Re: Divide Measure by Count of Hours in the day with Measure Value > 0

Hi Steven,

Dimension Datetime > Rightclick > Textmenu > Custom Date > Details: Days

1 of 1 people found this helpful
• ###### 8. Re: Divide Measure by Count of Hours in the day with Measure Value > 0

I see.

Well thanks again.  That's given me a lot to work with.

I really appreciate you support