1 2 Previous Next 19 Replies Latest reply on Nov 15, 2018 7:27 AM by Ombir Rathee

# Tricky LOD issue

I have a tricky LOD issue with a project I am working on.

I'm trying to calculate Fill rate based on invoice date by the first invoice occurrence per order. then look at all the units and divide the total units per order (multiple invoices) by the first invoice.

I got the calculation to work correctly with order number on the view, but when I remove order number the total calculation becomes incorrect.

For example in week 44 we shipped 60,263 units, on 685 order numbers, and 685 invoices. of those 685 orders our customers ordered 68,080 units. so 88.5% filled. not 37% filled.

it looks like the calculation is ignoring my filter for non-null values on the sum of units on the 1st invoice. Units of 1st Invoice

IF  [Line Status]='invoiced' and

{ FIXED [Order Number]: MIN(IIF([InvoiceNumber] > 0, [InvoiceNumber], NULL))}=[InvoiceNumber]

THEN [Units] END

Total Ordered

{ FIXED [Order Number], [Units of 1st Invoice]: sum([Units])}

% filled

sum([Units of 1st Invoice])/sum([Total Ordered])  • ###### 1. Re: Tricky LOD issue

Robin Fay wrote:

...

it looks like the calculation is ignoring my filter for non-null values on the sum of units on the 1st invoice.

You have too many details in there to juggle without an actual workbook to follow along in.

But maybe it all boils down to the one sentence I quoted above.

If you want a FIXED LOD to be impacted by a filter, right click on that filter and select "add to context".  By default, FIXED LODs are evaluated before filters.  But if you put it into context, that moves the filter before the LOD in Tableau's order of operations.

If that's not what you need, can you upload a workbook?

• ###### 2. Re: Tricky LOD issue

Here is an example file, hope this works

• ###### 3. Re: Tricky LOD issue

Hi Robin,

I suspect the Total Ordered Formula

Change to Include:

{ INCLUDE [Order Number], [Units of 1st Invoice]: sum([Units])}

Try is it working or not

• ###### 4. Re: Tricky LOD issue

It seems like your Units of 1st invoice calculation is also incorrect. I spent a lot of time to solve this but its really hard to understand what you want to accomplish. See this image and tell us for each order number what your both calculated fields(1st Invoice and total order) should return. • ###### 5. Re: Tricky LOD issue

I am attempting to show the units from the first invoice number where the line status is 'invoiced' for each order number. then use the cancel code as a filter

*after looking at this a bit more I might switch it to use min(date) in place of min(invoice number) but should essentially be the same.

3804982 would be 233 units on the first invoice divided by 233+409+8+3+21=674 (429 would be filtered out as it's 'customer requested')

233/674 = 34.6% fill

3855743

576/576 = 100% fill

3857501

40/197 = 20.3% fill

3859020

3/5=60% fill

3860349

1/1 = 100% fill

• ###### 6. Re: Tricky LOD issue

For first order number why you are adding 8 and 5 in total. Let's say we are calculating for week number 43 or 44 for current year then both these should be excluded from total as date is 1/1/1900 ??

• ###### 7. Re: Tricky LOD issue

1/1/1900 is where the invoice date is zero

I want to look at the first invoice unit amount vs all units on the order number regardless of date or status, then use the cancel reason as a filter.

I changed the total ordered (total by order number) to the following which seems to be working.

{ EXCLUDE [Invoice Date]:sum({ FIXED [Order Number]: sum([Units])})}

• ###### 8. Re: Tricky LOD issue

So problem solved now ??? If you don't want to include the cancel order neither in First nor in total then put this filter into context else it will give incorrect sum.

• ###### 11. Re: Tricky LOD issue

almost, in your example above it's counting 3804982 twice, once on the week on the first invoice and again in the following week.

I want to be able to show, is for the week (of invoice) what orders shipped where the invoice was the first invoice on the order.

I'm only counting the order in the week of the first invoice vs all the units on the order.

• ###### 13. Re: Tricky LOD issue

not quite

for example I would only show invoice 2116558 for order 3804982, I wouldn't count it again in the next week. • ###### 14. Re: Tricky LOD issue

I don't understand your requirement. 2116558 is the first invoice of Week 43. That is why it is shown in Week 43. 2117172 is the first invoice of Week 44. Therefore 409 is visible in 44.

And when you say I wouldn't count it again in next week...what it exactly means. I am not counting it in next week. For total, as you said invoice date will be ignored so both invoices will be added to total for every week of that order. For Units of 1st invoice, 2116558 is the 1st invoice for Week 43 and 2117172 is the first invoice for Week 44. Where it is counting again ?

1 2 Previous Next