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: 
Not applicable

Set Analysis and Variables?

All-

How do I set up set analysis (with variables) to sum up specific values contained in the output of the variables?  Here's an example expression:

=sum({<Product.ProdName={'$(vProd1)'}>}

Product.ProdValue) + sum({<Product.ProdName={'$(vProd2)'}>}

Product.ProdValue) + sum({<Product.ProdName={'$(vProd3)'}>}

Product.ProdValue) + sum({<Product.ProdName={'$(vProd4)'}>}

Product.ProdValue)

The above expression works absolutely fine.  I need a totals column. That being said, the values contained in vProd1, vProd2, (so on...) contain either a "ProdA" or "ProdB" contained in the ProductName (which is pulled in based on the variable).  I need to sum up all values containing "ProdA" and sum up all values containing "ProdB" each in it's own column.  What's the best way of accomplishing this?

Also...  I have to imagine this can be highly intensive (or could be).  I've provided a highly shorted version of this expression above.  Additionally, there are always hundreds of Products being added everyday.  So, I don't want to run into an "Out of memory" situation.  So also looking for efficient ways of handling this situation as well.

Thanks!

1 Solution

Accepted Solutions
swuehl
MVP
MVP

me again,

I just checked, for total of the four selections,

you could also write expression like

=sum({<Product={'$(vProd1)'}+{'$(vProd2)'}+{'$(vProd3)'}+{'$(vProd4)'}>} ProductValue)

instead of the four sums (like you used in OP).

And, to filter only ProdA containing variables, you might use

=sum({<Product=({'$(vProd1)'}+{'$(vProd2)'}+{'$(vProd3)'}+{'$(vProd4)'})*{'*ProdA*'}>} ProductValue)

(ProdB is trivial, of course)

Maybe this helps,

Stefan

View solution in original post

12 Replies
swuehl
MVP
MVP

Hi,

you can use set expression also with search strings, like

sum({<Product.ProdName={"*ProdA*"}>} Product.ProdValue)

which should limit the ProdNames to those containing ProdA.

Alternatively, you might want to have a look at the wildmatch() function, you can call functions from set expression using dollar sign expansion.

Stefan

swuehl
MVP
MVP

me again.

I am just curious:

Do you use this kind of expressions to compare a user selection (of 4 products) to total numbers?

If you tell us a bit more about your business requirement, I think someone here in the forum might be able to answer your performance question.

Stefan

Not applicable
Author

Hey swuehl-

Thanks so much for your help, that being said:

=sum({<Product.ProdName={'$(vProd1)'}>} Product.ProdValue)

vProd1 is actually a variable.  I have an input selector where the user defines the value of the field.  So, using your example that you presented... how would one adjust the expression?

Thanks!

swuehl
MVP
MVP

Well,

it should look like

=sum( {<Product.ProdName = {"*$(vProd1)*"} >} Product.ProdValue)

if I understand your question correctly (I was aware of vProd1 being a variabel, I assumed that you use a literal for the total match like 'ProdA'.

My Variable vProd1 is defined as

='ProdA'

for example

Stefan

Not applicable
Author

Sorry, I'm not giving enough info.

the vProd1 variable actually is a drop down selector.  The Product Names (which appears in the drop down selector) are already defined in a table.  Within the Product Name itself contains either "ProdA" or "ProdB."

So essentially, it goes like this:

1. User has a drop down input selector.  He/she chooses a Product: ShoesProdA.

2. ShoesProdA appears in the straight table with a value of 4. Essential vProd1 (the variable) gets replaced with ShoesProdA, with a value of 4.

The user has up to 30 products that they can select (in my example, I only went up to 4 hence: vProd1, vProd2....)

Hope that clears things up.

swuehl
MVP
MVP

With all the variables around, I think I missed the bottom line

We started with a need for an expression for total column, summing up all ProdValues for ProdNames containing a certain search string, right?

I think that you could do that with either using a literal or variable.

Do we still have a problem at this place?

I don't understand "Essential vProd1 (the variable) gets replaced with ShoesProdA, with a value of 4."

Does that mean you rename the variable to its value (Variablename vProd1 gets ShoesProdA and new value is numerical value of 4)? If so, what's that for?

If you still got problems and in case it is feasible for you, please post a small sample file, I am really curious now...

Regards,

Stefan

Not applicable
Author

"We started with a need for an expression for total column"

I've already done this with the expression I originally posted.

"summing up all ProdValues for ProdNames containing a certain search string, right?"

Yes... this is *exactly* what I'm looking for.

Within the Product Names, it contains either "ProdA" *or* "ProdB"

"I think that you could do that with either using a literal or variable.

Do we still have a problem at this place?"

To define the Products, I'm using a variable called vProd1, vProd2, vProd3, vProd4.

These variables reference an input selector, when you click on the drop down (let's say for vProd1), a large dropdown list of Products appear. The user will select one of those products in the dropdown. Within the *name* of the Product in the dropdown that the user selects will contain either "ProdA" or "ProdB."

"I don't understand "Essential vProd1 (the variable) gets replaced with ShoesProdA, with a value of 4."

Does that mean you rename the variable to its value (Variablename vProd1 gets ShoesProdA and new value is numerical value of 4)? If so, what's that for?"

No, we don't rename anything, the user selects a Product from a dropdown list which is what defines the variable vProd1, vProd2, vProd3, or vProd4.

Not applicable
Author

Here's my sample QVW (and it's data) to help where I'm comin' from

I'd like to create a "ProdA" Total column and a "ProdB" Total column.

swuehl
MVP
MVP

Ok, now I think I get want you want...

...Something like attached?

Not the most elegant way, but your task is really a bit tricky.

Have a nice evening,

Stefan