12 Replies Latest reply on Oct 16, 2018 8:25 AM by Don Wise

# Calculation Help

I need to be able to calculate the number of reservations that do not have a gross revenue of \$0, based on the resv date time (gnr count calc is in the measures). So in the example in the attached workbook, it shows 17 gnr's. It should actually be 16. When I try to filter the gross revenue, removing anything that is \$0, it removes the gnr's but it also removes calls handled. How can I calculate this so it only removes the gnr's and not the calls handled?

• ###### 1. Re: Calculation Help

Hi Barb,

If i change your GNR Count to the following, then it keeps the number of calls handled the same at 31 but gets you to 16: ZN(LOOKUP(COUNT({FIXED [Confirm Num]: COUNT([Resv DateTime])}),0))

Change the SUM to a COUNT.  Thx, Don

• ###### 2. Re: Calculation Help

Barb- I just flipped INCLUDE to EXCLUDE and it returned GNR count 16.

Would this work?

Best,

Shiva.

• ###### 3. Re: Calculation Help

This works in my test workbook but not in my actual master workbook. Any ideas? I do have 2 filters in my workbook, one for site and one for the date, MY.

• ###### 4. Re: Calculation Help

This works in my test workbook but not in my actual master workbook. Any ideas? I do have 2 filters in my workbook, one for site and one for the date, MY.

• ###### 5. Re: Calculation Help

Add both filters to 'Context' and try.

• ###### 6. Re: Calculation Help

I did try that but nothing changed.

• ###### 7. Re: Calculation Help

Did you try Don's approach with filters in context? Could you post a screenshot?

• ###### 8. Re: Calculation Help

I did try Don's as well and did the context but still had the same numbers. I can't do a screen shot of my actual workbook as I can't share company data.

Could your calculation be counting differently? This is how I'm counting to get the 16. Basically it's counting any reservation that doesn't have a \$0 revenue and only doing a distinct count of resv date time:

• ###### 9. Re: Calculation Help

Hi Barb,

In the calculation I provided initially, I'm not using COUNTD at all, which may make a difference, but in your most recent post I think you said are using COUNTD?  That might change things?

ZN(LOOKUP(COUNT({FIXED [Confirm Num]: COUNT([Resv DateTime])}),0))

Thx, Don

• ###### 10. Re: Calculation Help

I tried updating it to countd and I still got 17.

Can you explain how the formula is not counting resv date time stamps when the gross revenue is \$0?

• ###### 11. Re: Calculation Help

I think I found a calc that will work. I asked one of my coworkers and this is what they suggested:

{INCLUDE [Confirm Num]: COUNTD(if [Gross_Revenue]>0 then [Resv DateTime] end)}

• ###### 12. Re: Calculation Help

Hi Barb,

Looks like that worked in the test workbook you provided us; glad it worked out.  Don