topic Re: How to a acheive CASE statement in Qlikview? in QlikView

How to a acheive CASE statement in Qlikview?

Wed, 25 Nov 2020 16:16:04 GMT

Hi Folks,

I have a requirement to create a group something like this,

There is a sales column and I have to create the groups as a filter for the sales amount.

Case when (Sales > 0 and Sales < 1000000)

then

"$0 to $1M"

when (Sales > 1000000 and Sales < 2000000)

then

"1M to 2M"

when (Sales >2000000 and Sales < 5000000)

then

"$2M to $5M"

else

">$5M'

Expected result should be:

$0 to $1M

$1M to $2M

$2M to $5M

>$5M

User should be able to see all of the above options in the filter and select them accordingly.

Kindly do the needful.

Thanks in advance.

Re: How to a acheive CASE statement in Qlikview?

sunny_talwar — Mon, 25 Sep 2017 12:22:59 GMT

Try nested if....

If(Sales > 0 and Sales < 1000000, Dual('$0 to $1M', 1),

If(Sales < 2000000, Dual('$1M to $2M', 2),

If(Sales < 5000000, Dual('$2M to $5M', 3), Dual('>$5M', 4))))

Fixed the expression by adding the Dual as per swuehl‌ below

Re: How to a acheive CASE statement in Qlikview?

swuehl — Mon, 25 Sep 2017 12:27:43 GMT

I think you missed accidently a Dual():

If(Sales > 0 and Sales < 1000000,

Dual('$0 to $1M', 1),

If(Sales < 2000000,

Dual('$1M to $2M', 2),

If(Sales < 5000000, Dual('$2M to $5M', 3), Dual('>$5M', 4) )

)

Re: How to a acheive CASE statement in Qlikview?

Colin-Albert — Mon, 25 Sep 2017 15:10:36 GMT

You may want to trap sales < 0 - if a customer only has a credit you don't want it classed in the >$5M bucket!.
Rare but it does happen!

either use

If(Sales < 1000000,

Dual('$0 to $1M', 1),

If(Sales < 2000000,

Dual('$1M to $2M', 2),

If(Sales < 5000000,

Dual('$2M to $5M', 3),

Dual('>$5M', 4)

)

How to a acheive CASE statement in Qlikview?

If(Sales < 0,

Dual('< $0', 0),

If(Sales >= 0 and Sales < 1000000,

Dual('$0 to $1M', 1),

If(Sales < 2000000,

Dual('$1M to $2M', 2),

If(Sales < 5000000,

Dual('$2M to $5M', 3),

Dual('>$5M', 4)

)