12 Replies Latest reply on Oct 30, 2018 8:27 AM by Jim Dehner

# Include Function: I'm doing something wrong

I essentially need to calculate the average of the sum of the number of records per hour and graph it on a dual axis graph.  I'm not sure if I am approaching this correctly in the attached.

The formula I use is:

{ INCLUDE [Hour]: SUM([Number of Records])}

One of the values dimensions is a string (TFH Result).  I count the # of tfh results by hour, and then also include a line of the average by hour.   The sum by hour is fine, but the average of the sum by hour is not.   It seems to work okay in the attached workbook but I don't think I am approaching this correctly as it isn't working properly in another workbook... can someone please advise?

**I'm using INCLUDE rather than fixed so I can filter by a number of different values**

• ###### 1. Re: Include Function: I'm doing something wrong

Hi Ben,

I was initially confused by this equation until I synchronised the dual-axis - its looking like its working correctly here. (i.e. the avg of 9 and 3 = 6).

Can you please upload a .twbx example of where this is not working ?

HTH

Peter

• ###### 2. Re: Include Function: I'm doing something wrong

Hi Ben

I'm a little confused on the goal

the line chart on shown below is correct - it is taken the avg of the 2 days "hourly buckets"

is that not what you wanted?

Jim

• ###### 3. Re: Include Function: I'm doing something wrong

Yeah, probably stupid of me to include a workbook where it was working.  Here is one where it isn't.

• ###### 4. Re: Include Function: I'm doing something wrong

Hi Jim, just posted the workbook in a reply to Peter below.  Thanks!

• ###### 5. Re: Include Function: I'm doing something wrong

see the attached

this is the formula

that would read in words for each day sum the records and when you bring it to the viz take the average at the level of the other dimensions in the viz (i.e. hours)

it returns this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 6. Re: Include Function: I'm doing something wrong

Hi Jim,

Thank you.  It does seem to work for this workbook.  However, when I use the same calc in the workbook with more data (was too large to upload), it is giving me incorrect info (always over 2x what it should be).  Can you think of any reason this may be? The formula is the exact same

• ###### 7. Re: Include Function: I'm doing something wrong

Ben I would have to see the data to see what it is doing

if you have other dimensions in the viz it will affect the calculations

Sorry

Jim

• ###### 8. Re: Include Function: I'm doing something wrong

There aren't any other dimensions in the viz.. the only difference is that I filtered out call end time data so it would only bring in a few days and a few hours of the data.  Hmmm strange.  Thanks though, I'll keep working at it

• ###### 9. Re: Include Function: I'm doing something wrong

help me understand what you are doing that is different from what you posted and also explain why you are avoiding Fixed -

Fixed works above the viz and can be converted to a dimension where Incclude or exclude can not

Jim

• ###### 10. Re: Include Function: I'm doing something wrong

There isn't anything I'm doing differently than what I posted.  It's the exact same view, nothing else is included.  The only thing that is not the same is that my workbook has a lot more data than the data I posted.  It included 24 hours each day, and 200+ million rows of data.

I was avoiding using fixed because I will need to filter by multiple dimensions and be able to see the average moved based on what is being filtered.  I was trying to avoid having to add to context, and also when I substituted fixed in, it was still giving different data

• ###### 11. Re: Include Function: I'm doing something wrong

Jim, just came about this randomly by typing different things in, but when I wrapped call end time in Date it seems to work.....?  I have no idea why this is

Any idea why that would work rather than the other variations? I tried your version, I tried datepart 'day', date part hour, etc and none of them were giving me... I like that it works but I am baffled as to why it works

• ###### 12. Re: Include Function: I'm doing something wrong

Hi

sorry I my initial response I made an additional change I did not mention

I changed your hour calc to this

hour== datepart('hour',([Call End Time]))

try adding that and the calculation change to see if it corrects the problem - if not I don't think I will be able to help you

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.