Is it possible to use the Expression Name in set analysis?

[#1]

Hi everyone,

I'm struggling to upload sample reports so i'll try and explain this best i can. as you can see below i have a few expressions. What i need to do is use the 'Growth%' expression name inside set analysis to create another expression.

The Formula that i have tried to use is =count({$< Growth% = {'>0.00'}>}PharmacyName) , since the growth% formula is too complex and it complicates things using set analysis inside set analysis if you know what i mean, i've tried to use the expression name inside set analysis instead. Is this not possible or not allowed in qlikview, i've tried to vary the formula:

=count({$< Growth% > {'0.00'}>}PharmacyName)

but it just brings back null() values. Please would you let me know if this actually is possible.

If you would like me to capture a few more examples please let me know.

Thanks.

[#2]

I could either use:

=count({$< ((sum( {$<Year= {2009}, Advertiser = {'X'},TerminalsFlag = {'1'}, Quarter = {'Q4'},ManufacturerGroup = {'X'} >} Sales )/sum( {$<Year= {2008},Advertiser = {'X'},ManufacturerGroup = {'X'},TerminalsFlag = {'1'} >} Sales))*100-100)/100 = {'>0.00'}>}PharmacyName)

(which doesn't work)

or

=count({$< Growth% = {'>0.00'}>}PharmacyName)

(which is meant to be the same thing, except simpler, also brings back null() values).

[#3]

Have you tried the classic if-solution?

count(
if(Growth%>0.00, PharmacyName, null()
)

Pretty sure it works with an expression as a name inside another expression like this. Just a quick thought..

[#4]

Tried that, brings back the value as null values.. What i need to find out is if Expression names eg. Growth% can be used in If statements and set analysis, tried your if statement, also tried:

=if(Growth% > '0.00', count({$< Growth% = {'>0.00'}>} PharmacyName), null())

before and it didn't work..

Beginning to loose steam here, think it a lost cause..

Thanks for your input.

[#5]

You won't be able to make a formula reference in a set expression in this way. I just tested it several ways and doesn't work in any of them though I don't think it would work for you in this case anyway. When you reference a formula in this way, it takes the returned value in the object context verbatim. In other words, if you have your 2008 formula pulling the count of pharmacies for 2008 (in whatever set expression you choose for selection), you simply get back the total in the context of your specified dimensions and any reference to that formula will simply return that number. Its really handy if you have a complicated formula that is reused several times in a pivot/straight table and you don't want the system recalculating it over and over again (like several columns showing variance from the 2008 total).

In your examples above, your first formula is close to working, we just need to retool the set expression a bit. I use set expressions in two ways:

1. To reference a calculated argument for a given field or

2. To create a context for a true/false statement

Number 1 is pretty simple and you use it several times above (Year = 2009, Advertiser = X, etc.). Number 2 is equally simple, but from a different angle. Instead of specifying a field to be directly queried, you will specify a field to set context. In your case, this would be PharmacyName. Now add a simple statement to evaluate and put it in the set expression like this: count({$<PharmacyName = {'=Year=2009'}>} PharmacyName). The set expression evaluates the statement inside the set expression selection and returns true or false in the context of the set. There is one big problem with this methodology for your exact problem - single quotes. The single quotes have to house the expression and cannot be used anywhere else since the second single quote means the end of the expression. In your case, there is a way around this. Don't use quotes in your set selections. It won't look good in the expression editor, but it works. This methodology applied and removal of the quotes to your first formula will end up looking like this:

=count({$<PharmacyName = {'=((sum( {$<Year= {2009}, Advertiser = {X},TerminalsFlag = {1}, Quarter = {Q4},ManufacturerGroup = {X} >} Sales ) / sum( {$<Year= {2008},Advertiser = {X},ManufacturerGroup = {X},TerminalsFlag = {1} >} Sales))*100-100)/100 >= 0'}>} PharmacyName)

Hopefully this works for you. Good luck!