topic Re: Set Analysis based with set expression in it in QlikView

Set Analysis based with set expression in it

vitaliichupryna — Wed, 10 Oct 2018 12:58:43 GMT

Hi All,

I need calculate numbers of orders that have "returns" more than 6.
Previously for this I used following expression:
Count({<Returns={"<6"}>}Orders)

Week ago we moved to new dataset and we haven't "returns" in DB. Also after this, it's not possible to calculate returns in the script, so I create set expression for returns: $(eReturns)
The question is following: How can I change my Count calculation to make it works with calculated expression?

Re: Set Analysis based with set expression in it

andrey_krylov — Wed, 10 Oct 2018 13:45:33 GMT

Hi Vitalii, can you show this expression eReturns?

Re: Set Analysis based with set expression in it

jayanttibhe — Wed, 10 Oct 2018 13:47:48 GMT

You said that "returns" more than 6.

But your set expression is for "returns" less than 6. Try this way

Count({<Returns={"=Returns > 6"}>}Orders)

Count({<Returns={"> 6"}>}Orders)

Both are fine in your case.

Re: Set Analysis based with set expression in it

sunny_talwar — Wed, 10 Oct 2018 13:50:38 GMT

May be this

Count({<Orders={"=$(eReturns) < 6"}>} Orders)

Re: Set Analysis based with set expression in it

vitaliichupryna — Wed, 10 Oct 2018 14:08:50 GMT

eReturns is:

Sum({<Date = {">=$(vStart) <=$(=vEnd)"}>}[Issued Quantity])

/(Sum({<Date = {"<=$(vStart)"}>}[Received Quantity])-Sum({<Date = {"<=$(vStart)"}>}[Issued Quantity]))

Re: Set Analysis based with set expression in it

vitaliichupryna — Wed, 10 Oct 2018 14:21:49 GMT

Unfortunately, it doesn't work

Re: Set Analysis based with set expression in it

sunny_talwar — Wed, 10 Oct 2018 14:23:33 GMT

I guess since your expression includes double quotes, the double quote within the double quote failed... try this may be

Count({<Orders={[=$(eReturns) < 6]}>} Orders)

Re: Set Analysis based with set expression in it

jayanttibhe — Wed, 10 Oct 2018 14:32:35 GMT

Please check if it is working -

Count({<Orders={

"=$(=(Sum({<Date = {">=$(vStart) <=$(=vEnd)"}>}[Issued Quantity])

/(Sum({<Date = {"<=$(vStart)"}>}[Received Quantity])-Sum({<Date = {"<=$(vStart)"}>}[Issued Quantity]))

)

< 6"

}>} Orders)

Re: Set Analysis based with set expression in it

sunny_talwar — Wed, 10 Oct 2018 14:37:34 GMT

I don't think this will work jayanttibhe‌ because of multiple reasons

1) You have double quotes within double quotes. You need to either use an escape sequence or use a differ thing for beginning double quote

2) You have a dollar sign expansion in the set modifier which will compare Order to a single value. I don't think that will work