4 Replies Latest reply on Oct 8, 2018 9:44 PM by lei.chen.0

    Repeating an output dimension based on maximum measure

    Raza Asghar

      Hello,

       

      I have a data set of ITEM ID, Zone, Location, and Qty. ITEM IDs are unique and can contain 1 or more Zones, which each can contain 1 or more unique Locations. Each Location has a Qty associated with it, as seen below:

       

      ITEM ID

      Zone

      Location

      Qty

      0004

      A1

      1004

      5

      1005

      8

      0005

      B1

      2002

      2

      B2

      2003

      2

       


      I am trying to create a new dimension based on a calculated field ("LocationMaxQty") which will output the Location with the MAX QTY, FIXED by ITEM ID. Currently, I am stuck at a calculated field:

       

      IF   { FIXED [Item Id]:MAX([On Hand Qty])}    = [On Hand Qty]            THEN    [Location Id]           ELSE ""              END

       

      This outputs something like this, where it spits out the Location in the row with the highest Qty---- (CURRENT OUTPUT) ----

       

      ITEM ID

      Zone

      Location

      LocationMaxQty

      Qty

      0004

      A1

      1004

       

      5

      1005

      1005

      8

      0005

      B1

      2002

      2002

      2

      B2

      2003

      2003

      2

       

       

      HOWEVER, I want this location to be outputted in every single row as the same value, something like below ----(IDEAL SCENARIO)-----

       

      ITEM ID

      Zone

      Location

      LocationMaxQty

      Qty

      0004

      A1

      1004

      1005

      5

      1005

      1005

      8

      0005

      B1

      2002

      2002

      2

      B2

      2003

      2002

      2

       

       

      For example, for ITEM ID 0004, Location 1005 is on both rows (under column: LocationMaxQty) since that location has the MAX QTY.

       

      Item ID 0005 is a little tricky because there are two different locations with the same QTY, which is also the MAX QTY for the ITEM. In this case, I'd like the first Location with the MAX QTY relative to the ITEM ID to be selected as the "MAX" Location and have that outputted in each row.

       

      Can you provide some guidance please? Much appreciated!

        • 1. Re: Repeating an output dimension based on maximum measure
          lei.chen.0

          Hello Raza,

           

          Please try this calculation.

           

          { FIXED [ITEM ID]: MIN(

          IF [Qty]={ FIXED [ITEM ID]: MAX([Qty])} THEN [Location] END)}

           

           

          Regards

          Lei

          1 of 1 people found this helpful
          • 2. Re: Repeating an output dimension based on maximum measure
            seraj alam

            Hi Raza,

             

            please use below calculation and follow these simple step you will get desired result.

            { FIXED [item ID],[Zone]: MIN(IF [Quantity]={ FIXED [item ID],[Zone]:MAX([Quantity])} THEN [Location] END)}

             

            1. I have created the calculated field and named it to "Location Max "
            2. Ctrl-click location and Location Max in Dimensions, right-click the highlighted fields and then select Create > Combined Field.
            3. Drag the combined Location & Location Max  (Combined) field to the far left on Rows.
            4. Right-click Location & Location Max  (Combined) on Rows, and then clear Show Header

             

            Refer to attach workbook for your reference.

             

            Please mark this answer as helpful or correct answer if it solves your question.

             

            Thanks

            Seraj Alam

            1 of 1 people found this helpful
            • 3. Re: Repeating an output dimension based on maximum measure
              Raza Asghar

              Thank you so much! Worked perfectly. So I have a question on the calculated field:

               

              { FIXED [ITEM ID]: MIN(

              IF [Qty]={ FIXED [ITEM ID]: MAX([Qty])} THEN [Location] END)}

               

              In this example, the inner FIXED for Item 0004, Location 1004, would spit out the Max Quantity fixed for the ITEM ID, which is 8. Then moving outwards, the IF statement says that IF the Quantity for that row (5) is equal to 8 (WHICH IT'S NOT), then spit out the Location for that row.

               

              So I'm confused on what that IF statement would pop out, since there's no ELSE, and then how that causes the outer FIXED to know to spit out the other Location (with the higher Quantity). Do you mind explaining so I understand how the field works? Thank you!!

              • 4. Re: Repeating an output dimension based on maximum measure
                lei.chen.0

                Hello Raza,

                 

                Yes, mixing LOD's is sometimes confusing.

                 

                I'd like to explain in the screenshot.

                 

                The point is that,

                - inner FIXED and outer FIXED work at each [ITEM ID]

                - IF works at each row level.

                 

                One more thing I'd like to mention is the behaviou of MIN/MAX function.

                 

                According to Logical Functions , "(MIN) Returns the minimum of

                (1) an expression across all records or

                (2) the minimum of two expressions for each record."

                 

                I didn't find any official document but the result looks like,

                for case (1) NULL is ignored, for case (2) NULL is the result.

                 

                Please let me know if there is anything unclear

                 

                 

                Regards

                Lei

                1 of 1 people found this helpful