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

[Anonymous]

I don't understand what you need exactly. Please provide an example within an Excel.

But the formula I marked red will be calculated without any dimension hierarchy.

A set expression is similar to a selection within a listbox. So this can't work.

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

[vishsaggi]

Can you share a sample app to look into? Are these two variable or fields? -> idh_qty_inv , idh_conv ?

Quite did not get what that line underlined in the below expr does?

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

[Dayna]

Hi All,

I've modified the set analysis statement slightly and included in my copy attached, it's nearly there, but the expression is bringing back the same value for all rows whereas it should bring back the value depending on the combined value (i.e. SHAWGB5, SHAWGB6, etc..).

Hopefully the attached makes sense!

Many thanks for your help,

Dayna

[vishsaggi]

Would you mind telling us the exact output for some values in that table?

[Dayna]

Which values do you mean sorry? The concatenated string that I'm comparing against or the cost I want to display?

[vishsaggi]

Your expected output i mean. I can see three expressions there.

[Dayna]

Ah I see! For this expression:

sum({<PalletSpaceCost_CombinedValue={$(=PalletSpaceCost_Area&(ceil(sum(if(ad_zone =1, (idh_qty_inv/idh_conv), ((idh_qty_inv/idh_conv)/2)))))) }>} PalletSpaceCost_Cost)

Which has the inputs of:

SHAWGBWN2

SHAWGBWN1

SHAWGBWN4

SHAWGBWN2

SHAWGBWN1

SHAWGBWN1

So I'd expect:

PalletSpaceCost_CombinedValue	PalletSpaceCost_Cost
SHAWGBWN2	83.00
SHAWGBWN1	58.00
SHAWGBWN4	110.00
SHAWGBWN2	83.00
SHAWGBWN1	58.00
SHAWGBWN1	58.00

Hope that helps!!!

[vishsaggi]

Ok will look into it.

[vishsaggi]

There is one thing we have to use to get the correct cost. Still trying to find out. However, would like to take some suggestions from stalwar1‌.

Hello sunny, when you get time can you look into this attached qvw file.

Mainly the expression which has a set analysis. Need to display the cost of that specific combinedname.