How to a acheive CASE statement in Qlikview?

[#1]

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.

[sunny_talwar]

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

[swuehl]

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

     )

)

[Colin-Albert]

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?

or

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)

               )

          )

     )

)