3 Replies Latest reply on Jun 8, 2018 5:27 AM by Lasse Husman

    Why is nested LOD calc resulting in related calculated fields to move from dimension pane to measures pane?

    Lasse Husman

      Hi community.

      I am new to tableau but feel i have come off in good manor, despite I as most have to learn how to think differently when coming from an Excel / Power BI environment, so hope that this question still have some meat on the bone for some to help a Fellow tableau-learner.

       

      The short version:

      When I use a nested LOD expression in the dimension pane, it results in some other calculated fields in the dimension pane which referes to the nested LOD calc to move from the dimension pane to the measure pane with an added (Count) in the label and an '=ABC' icon in the front. This despite both dimensions returns text-labels and data type is strings.

      Why is this happening and what can I do to solve this issue (last if most likely not simple to answer based on given input) :-)

       

      For those that ask the for details he comes

      The long version:

      I am creating an automated invoice checker based on CSV-based invoices provided by the Courier.

      data is structured with a line per invoice charge element resulting in a tracking number defining a shipment will have multiple rows. So 1 row for Freight charge ([Charge description code] = FRT), 1 row for Fuel surcharge (FSC), 1 row for tax etc.

      FSC is a % of FRT value but differs between shipmenttype 'air' or 'ground'.

      Logic for air/ground is based on used service type that Only is defined in rows with FRT, but same column holds other values for FSC, TAX rows etc.

       

      So In order to get a calculated column called [Service] that repeats the result of a logic check for FRT cols I wrap the logic in Fixed LOD that Only look at [Tracking No] and [Charge Classification Code]='FRT', and then wrap that Again in an Exclude LOD that excludes the same [Charge Classification Code] to return the result from the inner LOD into all rows with same tracking number.

       

      So far so good it returns the result i am looking for... but the second I hit the Apply button i see an unwanted movement of another calculated field [Service shipment type] from dimension to the measure pane. It still has the '=ABC' icon and the data type is still a string, but the label gets a (Count) added to the label.

      The [Service shipment type] is using the [Service] field (with the nested LOD) to define if the shipmenttype is 'Air' or 'Ground' to be used to calc the correct the FSC charge.

       

      After working with it the [Service] (nested LOD) also moved to measures with (Attribute) attached to the label.

      Once they are there I can't pull it back to dimensions pan. Can be that I can't remember the last change i did, but when I try to undo I can't move the fields back Again either to the dimension pane and it seems to be stuck!

       

      I have not been able to find any related topics on tableau neither Google searches and hope somebody can explain why Tableau acts as it does so I can better understand this behaviour.

      If there are input to better ways to do what I try to do I love to hear them.

       

      Below table shows what i am trying to do, but my case have a lot more instances of services chargetypes and tracking numbers etc. :

            

      Tracking noChargeTypeService description[Service] (Nested LOD)[ShipmentType][Service] ruleShipmentType Rule
      AFreight ChargeXX-serviceAirLogic check the Service description for rows with ChargeType = "Freight Charge"and if "X" then return "X-service" in all rows with same tracking no.

       

       

       

       

      "Air" defined by Service="X-service" and
      "Ground" by Service="Y-service" and
      "??" if Service="?? Unknown service".

      AFuel surchargeasdX-serviceAir
      ATaxsdfX-serviceAir
      BFreight ChargeYY-serviceGroundLogic check the Service description for rows with ChargeType = "Freight Charge"and if "Y" then return "Y-service" in all rows with same tracking no.
      BFuel surchargeasdY-serviceGround
      BTaxsdfgY-serviceGround
      CFreight ChargeZ?? Unknown service??Logic check the Service description for rows with ChargeType = "Freight Charge"and if unknown then return "?? Unknown service" in all rows with same tracking no.
      CFuel surchargeasd?? Unknown service??
      CTaxsdfg?? Unknown service??

       

       

      I wrapped my nested LOD into a case to translate into text, to be able to use the nested LOD as it must be an aggregate.

      [Service] =

      CASE

      { EXCLUDE [Charge Classification Code] : MIN(

          {FIXED [Tracking Number] , [Charge Classification Code]='FRT'

      : MIN(

      //            IF [Charge Classification Code]='FRT'

      //                THEN

                          CASE [Charge Description]

                              WHEN 'X' THEN 1

                              WHEN 'Y' THEN 2

                              ELSE 99

                          END

      //            END

          )}

      )}

          WHEN 1 THEN 'X-service'

          WHEN 2 THEN 'Y-service'

          ELSE '?? unknown service'

      END

       

      [ShipmentType]=

      IF CONTAINS(lower([Service]),'X') THEN 'Air'

      ELSEIF CONTAINS(lower([Service]),'Y') THEN 'Ground'

      ELSE '??'

      END