11 Replies Latest reply on Jan 3, 2014 8:13 AM by Matt Lutton

Problem getting  the correct data

I have Hours Booked as a measure from my data for certain weeks upto 1st week of April as you can see between Booking Start Date and End Date. I also need to calculate Hours Available for those particular weeks but since there are only entries in the database where there is a booking ( like for eg for some days there is no booking so no data) , so the Hours Available calculation comes out wrong when I am trying to find Hours Available by week. Also when I aggregate it by Name or Geo it comes out wrong. How can I correct this?

Also I need to fill Hours Booked as 0 in weeks when I have no entries at all and Hours Available as 40. How to do that as well?

• 1. Re: Problem getting  the correct data

I'm having a hard time understanding the first part of your question.  Do you know what you are expecting as the result in View 1?

For part 2, for hours booked, you can use:

zn(lookup(sum([Hours Booked]), 0)) -- This will replace the blank values with 0s in View 2.  I'm a little unclear on the logic for setting Hours Available to 40.

• 2. Re: Problem getting  the correct data

Here's my go at fixing View 2 based on your description.
Hours Available Revised Calc is:

IF sum([Hours Available])>0 then SUM([Hours Available]) else 40 end

And Hours Book Revised Calc is:

zn(lookup(sum([Hours Booked]), 0))

Tableau 8.1 workbook attached.  If you can clarify your expectation for View 1, I will try to take a look at that.

Thanks for posting a packaged workbook!

• 3. Re: Problem getting  the correct data

So basically, the Hours available calculation provides Hours available for rows which have entries in the week for Hours Booked ( which I have as a column in the database) Attached find the excel sheet as well. Like for instance if the week of 29 dec for name B has 2 entries it will provide Hours available but what about the remaining 3 days? We dont get those 24 ( 8 + 8 +8) in my calculation for that week when I put Hours Available in the view

Hence when I am finding Hours Available by name or Geo  it only gives a partial view of Hours Available which is incorrect.

Thanks for the Lookup function approach. That should work for putting 0's.

• 4. Re: Re: Problem getting  the correct data

Attached is the excel sheet.

• 5. Re: Problem getting  the correct data

Couldn't you also just make sure there are always 5 days listed on the Excel sheet?  Does using the Hours Available Revised calculation in View 1 produce the results you expect?

I'm not an expert on any of this, but I participate to help myself learn and understand data better.  I am just not clear on the problem.

• 6. Re: Re: Problem getting  the correct data

Yeah that the problem. From Business Objects query it only returns the rows in excel for which there is an entry for the Booking Week.

• 7. Re: Re: Problem getting  the correct data

It will solve the problem when the weeks for which Hours Booked are 0 But look at below data week Dec 29,2013 for name B. The Hours available should be really 40-11.43 = 28.6 but its showing 4.57 and the reason it is doing that is

Sample data

 29-Dec-2013 04-Jan-2014 5.71429 29-Dec-2013 04-Jan-2014 5.71429

Hours Available calculation for these two rows (8 - 5.71429) = 2.28 and twice of that is 4.57

but actually the person is available for 3 more days right since there are only two entries so it should be 4.57+8+8+8

Did you understand?

• 8. Re: Re: Problem getting  the correct data

Should be name A in the above comment sorry

• 9. Re: Re: Problem getting  the correct data

I understand what you are saying, but I'm still unclear.  Some dates show more than 5 entries, so I'm not sure I understand the logic--for example:

 A US 22-Dec-2013 28-Dec-2013 8 A US 22-Dec-2013 28-Dec-2013 8 A US 22-Dec-2013 28-Dec-2013 5.71429 A US 22-Dec-2013 28-Dec-2013 5.71429 A US 22-Dec-2013 28-Dec-2013 5.71429 A US 22-Dec-2013 28-Dec-2013 5.71429 A US 22-Dec-2013 28-Dec-2013 5.71429
• 10. Re: Problem getting  the correct data

Oh. Yea thats an extreme case. It can be max 7 days a week since sometimes they overbook. so in that case if the total hours booked for that week is greater than 40 so the available hours should total to 0 ofcourse.Yeah that logic should also be inbuilt.

• 11. Re: Problem getting  the correct data

I'm going to step out of this thread and let someone with more experience chime in, and hopefully I will learn something new as well.