Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
Hi,
These two expression are working for me.
Can u post ur qvw file?
Regards,
Kabilan K.
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 "-"
Hi,
See the attached file..
These two expression are working.
~kabilan K
S, I unterstood. Thanks
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?
use aggr function
aggr(num({$<Product={(TargetProduct)}>})count(distinct [Customer]))
hope it helps you
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
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
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.