Set analysis to equal a calculated concatenated value

[Dayna]

Hello All!

This might sound simple, but I'm struggling with it... I'm trying to write a set analysis statement that does a calculation if a combined value equals another. So, for example

I have some fields to get prices:

Area	Price
ABC1	1
ABC2	2
ABC3	3

I also have another set of information that has the area, but the leading number changes based on a calculation to work out the quantity.

AreaGroup	Number of Items
ABC	1
ABC	2

To get the number of items, this is the calculation I'm doing:

ceil(sum(if(ad_zone =1, (idh_qty_inv/idh_conv), ((idh_qty_inv/idh_conv)/2))))

What I need to do, is, if AreaGroup & Number if Items = Area, then use the price. However, as I don't have both of these fields exposed in the table I need to do a set statement. I was thinking something like:

sum({<Area={$(=AreaGroup&(ceil(sum(if(ad_zone =1, (idh_qty_inv/idh_conv), ((idh_qty_inv/idh_conv)/2)))))&'`',',') }>} 1)

But this isn't working... Can anyone suggest any method to use a concatenated field with a calculation to get the resulting number, please?

Many thanks!!

Dayna

[sunny_talwar]

I am not sure what is needed here.... Is this needed in the Freight Table Export - Detail table or are we talking about creating a new table.

Also, I feel like we cannot use Set Analysis since we want the set modifier expression to be evaluated on every dimension. Set analysis cannot do this, so I feel the alternative might be to use Aggr().

Best,

Sunny

[Anonymous]

I think you'll need a separate dimension either as aggr() or within the script... but I don't understand it too...

[vishsaggi]

Yes, we have to use Aggr Function here. I will tell you the details. Select the values as i did and if you see for the expr palletCost, i have been trying to get the cost value like 126, 150, 150 for its respective Area and PalletSpaces. But it always giving 0.00. Let me know if i am not clear.

[sunny_talwar · Accepted solution]

May be this?

Sum(Aggr(If(PalletSpaceCost_Area&(ceil(sum(if(ad_zone =1, (idh_qty_inv/idh_conv), ((idh_qty_inv/idh_conv)/2))))) = PalletSpaceCost_CombinedValue, PalletSpaceCost_Cost), ih_ship, ih_ship_date, ad__chr02, PriceFile_Miles, PalletSpaceCost_Area, PalletSpaceCost_CombinedValue))

[vishsaggi]

Cant believe my self i did tried this way, but missed few Aggr dimensions to be added. So bad. Thanks a ton. To my understanding this is what Dayna looking for. Will have to check.

Dayna, please use Sunny's expression and let us know if this is what you are looking for.

[Dayna]

It works!! Thank you all so much, appreciated!!!

Vishwarath, thank you for your help especially in getting us there