Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Dayna
Creator II
Creator II

Set analysis to equal a calculated concatenated value

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:

 

AreaPrice
ABC11
ABC22
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.

  

AreaGroupNumber of Items
ABC1
ABC2

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

1 Solution

Accepted Solutions
sunny_talwar

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))

Capture.PNG

View solution in original post

15 Replies
Anonymous
Not applicable

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
Champion III
Champion III

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
Creator II
Creator II
Author

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
Champion III
Champion III

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

Dayna
Creator II
Creator II
Author

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

vishsaggi
Champion III
Champion III

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

Dayna
Creator II
Creator II
Author

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_CombinedValuePalletSpaceCost_Cost
SHAWGBWN283.00
SHAWGBWN158.00
SHAWGBWN4110.00
SHAWGBWN283.00
SHAWGBWN158.00
SHAWGBWN1

58.00

Hope that helps!!!

vishsaggi
Champion III
Champion III

Ok will look into it.

vishsaggi
Champion III
Champion III

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.