topic Re: Set analysis syntax in QlikView

Set analysis syntax

Sat, 29 Mar 2014 20:27:12 GMT

hi!

i have the following expression however it does not seem to be working correctly.

=if(Sum({1<Kund.Landkod={'DE'}>}$(vNettoFsg1)) > 1000

,'qmem://<bundled>/BuiltIn/led_g.png'

,if(Sum({1<Kund.Landkod={'DE'}>}$(vNettoFsg1)) < 1000

and Sum({1<Kund.Landkod={'DE'}>}$(vNettoFsg1)) > 500, 'qmem://<bundled>/BuiltIn/led_o.png',

'qmem://<bundled>/BuiltIn/led_r.png'))

i should recieve the led_o colour in this example as i know the Sales where 637 however it shows as green.

PS: i can solve this with a simple if however i would like to force this result to be shown even if the country is not selected, ie. Germany(DE) in this case.

Best

Brad

Re: Set analysis syntax

swuehl — Sun, 30 Mar 2014 00:04:02 GMT

That's hard to answer without knowing your data, model etc. It's often easier if you can post a small sample application that demonstrates the issue.

Your syntax seems correct on first glance.

In which context are you using this expression and how have you checked the actual value of Sales (637)?

Re: Set analysis syntax

arsal_90 — Sun, 30 Mar 2014 03:23:39 GMT

Please provide sample data in excel and please be sure these fields are present that you are using in the expression

Re: Set analysis syntax

Sun, 30 Mar 2014 06:28:01 GMT

hi i have attached a sample now.

any help is greatly appreciated

Re: Set analysis syntax

swuehl — Sun, 30 Mar 2014 11:47:51 GMT

Bradley,

would be good if you can point us to the issue using the attached document, I don't really like to guess around

If you are referring to the text box in the map, saying 2711,18036, as a reference value, you are using as expression

=Sum({<Kund.Landkod={'DE'}>}$(vNettoFsg1) /1000)

In the expression used for the bullet point near by, you are using

=if(Sum({1<Kund.Landkod={'DE'}>}$(vNettoFsg1) /1000) > 1000

,'qmem://<bundled>/BuiltIn/led_g.png'

,if(Sum({1<Kund.Landkod={'DE'}>}$(vNettoFsg1) / 1000) < 1000

and Sum({1<Kund.Landkod={'DE'}>}$(vNettoFsg1) /1000) > 500, 'qmem://<bundled>/BuiltIn/led_o.png',

'qmem://<bundled>/BuiltIn/led_r.png'))

Besides that I see a different value than you posted in your original post (637), and that the value I see should return a green icon, there is an important difference between the two calculations, the set identifier.

In your image expression, you are using a set identifier 1, ignoring all your user made selections.

Sum({1<Kund.Landkod={'DE'}>}$(vNettoFsg1) /1000)

Re: Set analysis syntax

PrashantSangle — Sun, 30 Mar 2014 16:18:31 GMT

Hi,

In your first if you are evaluating

Sum({1<Kund.Landkod={'DE'}>}$(vNettoFsg1)) > 1000 if this false then

if(Sum({1<Kund.Landkod={'DE'}>}$(vNettoFsg1)) < 1000 // This condition automatically true so dont include this

and Sum({1<Kund.Landkod={'DE'}>}$(vNettoFsg1)) > 500

So Try this,

=if(Sum({1<Kund.Landkod={'DE'}>}$(vNettoFsg1)) > 1000,'qmem://<bundled>/BuiltIn/led_g.png',

if(Sum({1<Kund.Landkod={'DE'}>}$(vNettoFsg1)) > 500,

'qmem://<bundled>/BuiltIn/led_o.png','qmem://<bundled>/BuiltIn/led_r.png'))

Regards,