topic Re: Set Analysis Expression in QlikView

Set Analysis Expression

alec1982 — Mon, 29 Jul 2013 19:15:53 GMT

Hi Guys,

Anybody can tell me what's wrong with the below expression:

Sum({$<[Description] = {"*"} - {"Sold"}>}[Amount])

Thxs,

Alec

Re: Set Analysis Expression

Anonymous — Mon, 29 Jul 2013 19:19:11 GMT

Alec, try this:

Sum({$<[Description] =- {'Sold'}>}[Amount])

Re: Set Analysis Expression

alec1982 — Mon, 29 Jul 2013 19:20:55 GMT

Thxs for the quick reply but this is giving me an error..

Re: Set Analysis Expression

Anonymous — Mon, 29 Jul 2013 19:21:28 GMT

I just changed a little, try again

Re: Set Analysis Expression

alec1982 — Mon, 29 Jul 2013 19:27:55 GMT

Hi Michael,

Thanks for all your help. this still getting an error..

The reason i have double quotes "Sold" is beacuse the description is sometimes just Rent and sometime is Monthly Sold..

Re: Set Analysis Expression

jaime_aguilar — Mon, 29 Jul 2013 19:31:45 GMT

Both Set analysis expressions are ok. What happens here is a syntax parser bug. It doesn't matter if you type the expression like this: Sum({$<[Description] = {"*"} - {"Sold"}>}[Amount])

or like this: Sum({$<[Description] =- {'Sold'}>}[Amount]) , both examples will cause that the syntax parser underlines part of the expression like if there was something wrong but it's not. In fact if you look at the upper left corner of the expression editor, you will see the label "Expression OK" telling you that everything is alright.

Btw, this is a known issue, however it has not been fixed yet

Re: Set Analysis Expression

Anonymous — Mon, 29 Jul 2013 19:31:53 GMT

The syntax is correct, I tested it. Make sure that fiald names are not mistyped.

If you to exclude a value that contains 'Sold', not just equal 'Sold', you have to use * :

Sum({$<[Description] =- {'*Sold*'}>} [Amount])

Re: Set Analysis Expression

Anonymous — Mon, 29 Jul 2013 19:35:31 GMT

Jaime,

You think Alec is concerned about that error? 🙂

Alec, it is not an error. Error is when you don't get the expected result...

Re: Set Analysis Expression

alec1982 — Mon, 29 Jul 2013 19:37:02 GMT

Hi,

Thank you for the answer.. but not only the expression is giving me red line but it also doesn't give me the right answer..

Best,

Alec

Re: Set Analysis Expression

Anonymous — Mon, 29 Jul 2013 19:39:02 GMT

Can you upload an example? The expression is rather simple, I can't guess what's wrong in this case.

Re: Set Analysis Expression

alec1982 — Mon, 29 Jul 2013 19:41:50 GMT

Hi,

I am not able to load a sample but the issue is that the expression is not filtering based on the set analysis..

Sum({$<[Description] ={"*"}-{"Sold"}>}[Amount])

It is giving me all the total amount including the Sold items..

Best,

Alec

Re: Set Analysis Expression

alec1982 — Mon, 29 Jul 2013 19:47:24 GMT

Hi,

this seems to be working for me :

=Sum({$<[Description] = {'*'} - {"*Sold*"}>}[Amount])

Thxs for all your help!

Re: Set Analysis Expression

Anonymous — Mon, 29 Jul 2013 19:48:13 GMT

Alec,

This expression is as good as the one I sent you, and should work the same way. That is, it should exclude Amounts where Desription is equal 'Sold'. Possible reason of not working:

- There are no records where Description='Sold'. Use '*Sold*' to remove records where Description contains 'Sold'

- Data relations. Canb't guess from here...

Regards,

Michael

Re: Set Analysis Expression

Anonymous — Mon, 29 Jul 2013 19:48:44 GMT

Great!

🙂