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

# Repeating an output dimension based on maximum measure

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

Hello Raza,

{ 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

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.

Thanks

Seraj Alam

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

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

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