Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
laujerry
Creator
Creator

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!

1 Solution

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

10 Replies
Not applicable

Hi,

These two expression are working for me.

Can u post ur qvw file?

Regards,

Kabilan K.

laujerry
Creator
Creator
Author

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

Hi,

See the attached file..

These two expression are working.

~kabilan K

Not applicable

S, I unterstood. Thanks

laujerry
Creator
Creator
Author

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
Master II
Master II

use aggr function

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

hope it helps you


kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable

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

Gysbert_Wassenaar

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