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.
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!
Sample Tableau ML View 2.twbx 48.0 KB
Thanks for your reply.
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.
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.
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.
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
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?
Should be name A in the above comment sorry
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
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.
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.