topic Re: Problem with Set Analysis in QlikView

Problem with Set Analysis

laujerry — Wed, 19 Dec 2012 04:00:33 GMT

Hi everyone, I am a newbie to QlikView and I am now studying the use of set analysis

I want to use an expression to select the number of distinct customer who purchases certain product from lines of sales order data.

Please kindly describe what is the difference between

Count({$<Product={'$(TargetProduct)'}>} DISTINCT [Customer])

and

Count({$<Product={$(TargetProduct)}>} DISTINCT [Customer])

where TargetProduct is a variable name and can be changed by button click actions.

I suppose the second one should be used but it shows nothing while the first statement works.

Thanks!

Re: Problem with Set Analysis

Wed, 19 Dec 2012 05:04:14 GMT

Hi,

These two expression are working for me.

Can u post ur qvw file?

Regards,

Kabilan K.

Re: Problem with Set Analysis

laujerry — Wed, 19 Dec 2012 07:08:44 GMT

hi kabilan,

i am sorry the data source is from my company and cannot be shared here

but what i think is that the two expressions should be different

which the first one is trying to match the exact string specified in quotations

and the second one is to replace the variable with another value

i present the expression as a value of text field, sth like: =Count(....

the first expression shows me the correct number and the second displays "-"

Re: Problem with Set Analysis

Wed, 19 Dec 2012 07:34:11 GMT

Hi,

See the attached file..

These two expression are working.

~kabilan K

Re: Problem with Set Analysis

Wed, 19 Dec 2012 08:40:17 GMT

S, I unterstood. Thanks

Re: Problem with Set Analysis

laujerry — Mon, 24 Dec 2012 01:06:48 GMT

I am using the personal edition qlikview and cannot open your file

but btw, so do you mean the 2 expressions are syntactically the same?

Re: Problem with Set Analysis

er_mohit — Mon, 24 Dec 2012 06:32:15 GMT

use aggr function

aggr(num({$<Product={(TargetProduct)}>})count(distinct [Customer]))

hope it helps you

Re: Problem with Set Analysis

kaushiknsolanki — Mon, 24 Dec 2012 06:56:24 GMT

Hello,

You are right only first expression will work.

The difference is because of the Data Type of the parameter you are passing.

In your example you are passing the Target Product which is a String, and when ever you are passing String as a parameter it should be enclosed in single quote.

But if you are passing the Number as parameter, you dont need to enclosed it in quote.

And because of this your first expression works properly but not second.

Hope this will clear the doubt.

Regards,

Kaushik Solanki

Re: Problem with Set Analysis

Mon, 24 Dec 2012 07:21:45 GMT

Hi Kaushik Solanki,

We can write set anlysis for String with out single quote like below.

sum( {$<Region= {America} >} Sales ) /// It returns correct result

sum( {$<Region= {'America'} >} Sales ) /// It returns correct result

But below is wont give a result

sum( {$<Region= {Ame*} >} Sales ) // It won't work

that s Y we use a single quote mark

sum( {$<Region= {'Ame*'} >} Sales ) // It will work

I hope, all are understand

~Kabilan K

Re: Problem with Set Analysis

Gysbert_Wassenaar — Mon, 24 Dec 2012 09:23:31 GMT

The first works because the value in your variable isn't enclosed in single quotes. So after expanding the variable you have a filter like Product={'Cookies'}. But in the second expression it will be Product={Cookies}. That won't work because it will treat Cookies as a number because of the missing single quotes. If your variable had a value that included the quotes, i.e. 'Cookies','Candy' then the second expression would work and the first wouldn't. The second expression would have as filter Product={'Cookies','Candy'} to limit Product to Cookies and Candy.

Re: Problem with Set Analysis

sujeetsingh — Mon, 24 Dec 2012 10:23:33 GMT

A to the point answer by Gysbert..