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

    Tricky LOD issue

    Robin Fay

      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
          Joe Oppelt

          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
            Robin Fay

            Here is an example file, hope this works

            • 3. Re: Tricky LOD issue
              Akram Ebrahim

              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
                Ombir Rathee

                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
                  Robin Fay

                  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
                    Ombir Rathee

                    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
                      Robin Fay

                      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
                        Ombir Rathee

                        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.

                        • 9. Re: Tricky LOD issue
                          Robin Fay

                          not solved, the problem is when I take order number off the view the calculation stops working correctly

                           

                          • 10. Re: Tricky LOD issue
                            Ombir Rathee

                            I just filtered to 5 order numbers for testing. See below. Its seems to be working as you asked.

                             

                             

                            • 11. Re: Tricky LOD issue
                              Robin Fay

                              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.

                              • 12. Re: Tricky LOD issue
                                Ombir Rathee

                                Like this?

                                 

                                • 13. Re: Tricky LOD issue
                                  Robin Fay

                                  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
                                    Ombir Rathee

                                    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