topic Re: Set Expression help in QlikView

Set Expression help

Thu, 16 Oct 2014 09:14:22 GMT

Hello QV,

I have below expr in my chart.

sum({<Segment={'Group'}, Status = {'Cancelled'}>}[REV YTD])/1000000

Now my requirement is to exclude Cancelled Accounts, means Status is not equals to 'Cancelled'.

How to write the expr for the same.

Thanks,

Ananth

Re: Set Expression help

simondachstr — Thu, 16 Oct 2014 09:15:38 GMT

sum({<Segment={'Group'}, Status -= {'Cancelled'}>}[REV YTD])/1000000

A minus before the = should exclude it

Re: Set Expression help

alexandros17 — Thu, 16 Oct 2014 09:17:09 GMT

Try with

sum({<Segment={'Group'}, Status = {'*'}-{'Cancelled'}>}[REV YTD])/1000000

means all minus cancelled

Re: Set Expression help

Thu, 16 Oct 2014 09:21:31 GMT

Sorry, both are not working...

Re: Set Expression help

ashfaq_haseeb — Thu, 16 Oct 2014 09:24:08 GMT

Hi,

this must work

sum({<Segment={'Group'}, Status -= {'Cancelled'}>}[REV YTD])/1000000

If not post sample data.

Regards

ASHFAQ

Re: Set Expression help

Thu, 16 Oct 2014 09:29:44 GMT

I'm getting error, if I write above expr

Re: Set Expression help

alexandros17 — Thu, 16 Oct 2014 09:31:39 GMT

I'm sure the syntax is correct, explain with more details, why you say it doesn't work?

Re: Set Expression help

ashfaq_haseeb — Thu, 16 Oct 2014 09:32:39 GMT

It gives error but gives you proper result

To avoid that you have been provided second expression

Which does the same

sum({<Segment={'Group'}, Status = {'*'}-{'Cancelled'}>}[REV YTD])/1000000

Regards

ASHFAQ

Re: Set Expression help

mr_novice — Thu, 16 Oct 2014 09:33:03 GMT

The editor dont understand the syntax but it should work...

Re: Set Expression help

PrashantSangle — Thu, 16 Oct 2014 09:36:03 GMT

Hi,

Even if there is red line but it will give you correct output.

Check it.

Regards

Re: Set Expression help

martinpohl — Thu, 16 Oct 2014 09:39:22 GMT

This variant

sum({<Segment={'Group'}, Status -= {'Cancelled'}>}[REV YTD])/1000000

gives you the result of all status excluded Cancelled, indepentend of what status is selected

and this

sum({<Segment={'Group'}, Status = {'*'}-{'Cancelled'}>}[REV YTD])/1000000

gives you the result of selected values in status, excluded Cancelled if selected

Regards

Re: Set Expression help

Thu, 16 Oct 2014 09:43:22 GMT

In the image,

The same account is coming in both the chart. Why this is happening

Re: Set Expression help

ashfaq_haseeb — Thu, 16 Oct 2014 09:45:56 GMT

May be same account but with different revenue.

you need to look at your data.

Do some tests to validate the same.

Regards

ASHFAQ

Re: Set Expression help

Thu, 16 Oct 2014 09:53:24 GMT

Thank you very much to all, I'm verifying my data.

Re: Set Expression help

ashfaq_haseeb — Thu, 16 Oct 2014 09:55:10 GMT

Can you close this thread by selecting appropriate answers.

Regards

ASHFAQ