7 Replies Latest reply: Jun 26, 2018 11:54 AM by Sunny Talwar

# Case when..?

Hi,

I am trying to get a calculation but not sure how to go about doing it in Qlik Sense.

I have rows which I need to calculate in inches but some of the rows are in cm. How would I write the expression to say:

Sum(case when type = 'cm' then length/2.54 else length end as length)

Sum(case when type = 'cm' then height/2.54 else length end as length)

Sum(case when type = 'cm' then width/2.54 else length end as length)

I can then multiply together the above to get cubic inches.

PartIDTypeLengthHeightWidthCubic Inches
1cm10105=sum(10/2.54)*(10/2.54)*(5/2.54)
2cm12105=sum(12/2.54)*(10/2.54)*(5/2.54)
3inch555=sum(10)*(10)*(5)
4inch10155=sum(10)*(10)*(5)
5cm151010=sum(15/2.54)*(10/2.54)*(10/2.54)

Thank you in advance for the help.

• ###### Re: Case when..?

in your script you can do as below:

if( type = 'cm', length/2.54, length ) as length

I hope it helps.

• ###### Re: Case when..?

One option is to do this

RangeSum(

Sum({<Type = {'cm'}>}Length*Height*Width)/pow(2.54, 3),

Sum({<Type = {'inch'}>}Length*Height*Width)

)

• ###### Re: Case when..?

Thanks again Sunny!

I think that has worked, just need to cross-check the figures.

• ###### Re: Case when..?

Hi Sunny,

If I wanted to do the same but convert weight to lbs if it's kg, would the format be the same? I tried the following but isn't giving me the correct answer:

RangeSum(

Sum({<WeightUOM = {'L'}>}Weight)/pow(2.2046, 3),

Sum({<WeightUOM = {'K'}>}Weight)

)

• ###### Re: Case when..?

May be this

RangeSum(

Sum({<WeightUOM = {'L'}>}Weight)/2.2046,

Sum({<WeightUOM = {'K'}>}Weight)

)

• ###### Re: Case when..?

Thanks, I had to do the following:

RangeSum(

Sum({<WeightUOM = {'L'}>}Weight)/2.2046,

Sum({<WeightUOM = {'K'}>}Weight)*2.2046

)

Thanks again!

• ###### Re: Case when..?

okay cool