topic Re: Error using Set Analysis with Variable in QlikView

Error using Set Analysis with Variable

Wed, 01 Oct 2014 07:17:40 GMT

Hello everyone,

I need your help,

I have some troubles with an expression using set analysis and a variable too.

This is my exprssion in a pivot table :

sum({$ <{$(vAndSubFamily)$(vNotSubFamily)}, Type = 'Actuals' >} [Amount])

$(vAndSubFamily)$(vNotSubFamily) are two succesive products choices

Actuals is a filter on the Sales Type , I have other Types like Budget and Quotations.

Do you know where the error comes from ?

Thank you,

Have a nice day,

David

Re: Error using Set Analysis with Variable

tresB — Wed, 01 Oct 2014 07:20:27 GMT

The proto-type is like:

Sum({<[Product Choice]={'Product1', 'Product2'}, Type={'Actuals'}>}Amount)

Re: Error using Set Analysis with Variable

ashfaq_haseeb — Wed, 01 Oct 2014 07:22:07 GMT

Try this

sum({$ <{Product={'$(vAndSubFamily)','$(vNotSubFamily)'}, Type = {'Actuals' }>} [Amount])

Make sure to replace it with your original fields.

Regards

ASHFAQ

Re: Error using Set Analysis with Variable

Wed, 01 Oct 2014 07:37:19 GMT

Hello treseco and ASHFAQ,

Thanks for your really quick response !

My initial expression is : sum({$(vAndSubFamily)$(vNotSubFamily)}Amount)

And actually works, I just add a filter on Type and I have error.

I tried your two suggestions and it's not working with the filter on the Type.

ASHFAQ, is it mandatory to put "SubFamily=" before my variable ? Because it's working without in my initial expression.

Regards

David

Re: Error using Set Analysis with Variable

ashfaq_haseeb — Wed, 01 Oct 2014 07:43:51 GMT

Hi,

This is not set analysis

sum({$(vAndSubFamily)$(vNotSubFamily)}Amount)

For set Analysis

You need to go with the above syntax mentioned.

Regards

ASHFAQ

Re: Error using Set Analysis with Variable

Wed, 01 Oct 2014 07:50:46 GMT

Hi,

I tried,

sum({$ <{SubFamily={'$(vAndSubFamily)','$(vNotSubFamily)'}, Type = {'Actuals'}>} [Amount])

but I have still error on the third curly bracket,

do you have idea why?

Regards

David

Re: Error using Set Analysis with Variable

ashfaq_haseeb — Wed, 01 Oct 2014 07:55:46 GMT

Try this

=sum({$ <SubFamily={'$(vAndSubFamily)','$(vNotSubFamily)'}, Type = {'Actuals'}>} [Amount])

Regards

ASHFAQ

Re: Error using Set Analysis with Variable

Wed, 01 Oct 2014 08:00:33 GMT

You're right,

when I add "=" my error disappear.

But my pivot table still shows nothing.

Regards

David

Re: Error using Set Analysis with Variable

ashfaq_haseeb — Wed, 01 Oct 2014 08:04:17 GMT

Hi,

Can you post a sample application to look at.

Regards

ASHFAQ

Re: Error using Set Analysis with Variable

ashfaq_haseeb — Wed, 01 Oct 2014 08:11:14 GMT

It was not just =

have a look at your expression

sum({$ <{SubFamily={'$(vAndSubFamily)','$(vNotSubFamily)'}, Type = {'Actuals'}>} [Amount])

Them mine

=sum({$ <SubFamily={'$(vAndSubFamily)','$(vNotSubFamily)'}, Type = {'Actuals'}>} [Amount])

Can you try this and let me know.

If that dosen't work then attach sample.

Regards

ASHFAQ

Re: Re: Error using Set Analysis with Variable

Wed, 01 Oct 2014 08:18:33 GMT

Hi,

Yes I use excatly your expression and still doesn't works.

Please find here a sample,

The Customer pivot uses your expression.

Regards

David

Re: Error using Set Analysis with Variable

jyothish8807 — Wed, 01 Oct 2014 08:20:23 GMT

Hi Try this,

=sum({$ <SubFamily={'$(=vAndSubFamily)','$(=vNotSubFamily)'}, Type = {'Actuals'}>} [Amount])

If you have created variables in variable wizard then put = in the begining of defination.

Regards

Re: Error using Set Analysis with Variable

tresB — Wed, 01 Oct 2014 08:22:52 GMT

Check your variables, they are not producing expected output.

Re: Error using Set Analysis with Variable

ashfaq_haseeb — Wed, 01 Oct 2014 08:25:32 GMT

yes there is no proper o/p in your variables.

Regards

ASHFAQ

Re: Error using Set Analysis with Variable

Wed, 01 Oct 2014 08:36:14 GMT

Hi,

Sorry, but I don't understand what you mentioned,

Can you give me an example please ?

Regards

David

Re: Error using Set Analysis with Variable

Wed, 01 Oct 2014 08:38:20 GMT

Hi KC,

thanks for your proposition but still not working,

the problem is from elsewhere apparently

Regards,

David

Re: Re: Error using Set Analysis with Variable

ashfaq_haseeb — Wed, 01 Oct 2014 08:39:35 GMT

Hi,

have a look at attached image.

Regards

ASHFAQ

Re: Error using Set Analysis with Variable

Anonymous — Wed, 01 Oct 2014 08:56:14 GMT

Hi David,

Please check your variable 'vAndSubFamily' and 'vNotSubFamily' here when I check these variable in text box then in first variable gives output '$Field' and second gives no data.

Thanks

Re: Error using Set Analysis with Variable

Wed, 01 Oct 2014 09:06:22 GMT

Hi Dhiraj,

Thanks for your post,

I tried that but still not working :

vNotSubFamily :

=if(count({NOT} distinct SubFamily)<>count({NOT<SubFamily>} distinct SubFamily), //some products selected (but not all) '*' & concat({$*NOT} distinct '<' & vRelevantField & '=E({<SubFamily={' & chr(39) & SubFamily & chr(39) & '}>} ' & vRelevantField & ')>','*',SubFamily), ' <$Field>')

Regards David

Re: Error using Set Analysis with Variable

Wed, 01 Oct 2014 09:07:33 GMT

ASHFAQ,

Sorry for my bad level ....

I tried that but still not working :

vNotSubFamily :

Regards

David