Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

laujerry
Contributor

Problem with Set Analysis

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!

Tags (2)
1 Solution

Accepted Solutions

Re: Problem with Set Analysis

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

10 Replies
Not applicable

Re: Problem with Set Analysis

Hi,

These two expression are working for me.

Can u post ur qvw file?

Regards,

Kabilan K.

laujerry
Contributor

Re: Problem with Set Analysis

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 "-"

Not applicable

Re: Problem with Set Analysis

Hi,

See the attached file..

These two expression are working.

~kabilan K

Not applicable

Re: Problem with Set Analysis

S, I unterstood. Thanks

laujerry
Contributor

Re: Problem with Set Analysis

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?

er_mohit
Honored Contributor II

Re: Problem with Set Analysis

use aggr function

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

hope it helps you


Re: Problem with Set Analysis

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

Not applicable

Re: Problem with Set Analysis

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

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.


talk is cheap, supply exceeds demand
Community Browser