6 Replies Latest reply on Oct 31, 2018 10:37 AM by maneesh.gaddam

# Fixed by one dimension, if at least one sub dimension = something, then assign fixed dimension....

I have a data set with unique display locations, each of which contain at least 1 ITEM ID. Each ITEM ID is either considered "Y" meaning its AGING, or "N" meaning its NOT AGING.

I want to create a calculated field whereby if a Display Locn has AT LEAST 1 ITEM ID that is considered "Y", then the overall output for that Display Locn is "AGING". If not, meaning, all of a Display Locn's Item IDs are "N", like (display locn 9201 & 8483), then the Display Locn would get a "NON AGING" output.

How can I do this? I'm stuck at the {FIXED: Display Locn} part, because I'm not sure how to tell a calculated field to check all of a dimension's sub dimensions aka Item IDs for a specific dimension (Y or N) and if it finds at least one that is "Y", then output "AGING" (display locn 1005 and 4003).

 Display Locn Item ID Aging OUTPUT 1005 8294971920 YY AGING 4003 091901819884820 NNY AGING 9201 92880192 NN NON AGING 8483 8283 N NON AGING

Thank you!

• ###### 1. Re: Fixed by one dimension, if at least one sub dimension = something, then assign fixed dimension....

Raza,

You can try the following

Calculation 1: {FIXED DisplayLocn: MAX(if Aging='Y' then [Item ID] END)}

Calculation 2: IIF(ISNULL(Calculation1),'NON AGING','AGING')

1 of 1 people found this helpful
• ###### 2. Re: Fixed by one dimension, if at least one sub dimension = something, then assign fixed dimension....

Thank you, so prompt!

• ###### 3. Re: Fixed by one dimension, if at least one sub dimension = something, then assign fixed dimension....

Can you explain how these 2 calculated fields work? Step by step? I am having trouble understanding how it's doing it. If you could walk through how the calculated fields work for each display locn in the table, I'd really appreciate that.

Thank you!!

• ###### 4. Re: Fixed by one dimension, if at least one sub dimension = something, then assign fixed dimension....

First calculation: Tableau pulls, for each Display Location,  Maximum of ID where Aging=Y. Here, it doesn't matter if we take Max or Min as your requirement only considers scenarios with Aging=Y. I just took Max for my reference. So, for [Display Locations], which have an Aging=Y, it takes Max ID and for other locations with no Aging=Y, it gives NULL.

Second Calculation: It is just saying, if first calculation is NULL then it doesn't have any Aging=Y record and if it isn't NULL, it does have a record so assign 'Aging'.

Hope this make sense.

• ###### 5. Re: Fixed by one dimension, if at least one sub dimension = something, then assign fixed dimension....

How would I perform the same logic but in this case I would want to know if a Display LOCN has all "Y"s for AGING, not just AT LEAST 1.

In this case, it would only spit out "AGING" for Display Locn 1005 and not the other ones, since Display Locn 1005 has all "Y".

Thanks.

• ###### 6. Re: Fixed by one dimension, if at least one sub dimension = something, then assign fixed dimension....

You can try

IF  {FIXED DisplayLocn: MIN(Aging)}='N' 'Not Aging' Else 'Aging' END