Case when..?

[Anonymous]

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.

PartID	Type	Length	Height	Width	Cubic Inches
1	cm	10	10	5	=sum(10/2.54)*(10/2.54)*(5/2.54)
2	cm	12	10	5	=sum(12/2.54)*(10/2.54)*(5/2.54)
3	inch	5	5	5	=sum(10)*(10)*(5)
4	inch	10	15	5	=sum(10)*(10)*(5)
5	cm	15	10	10	=sum(15/2.54)*(10/2.54)*(10/2.54)

Thank you in advance for the help.

[agigliotti]

in your script you can do as below:

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

I hope it helps.

[sunny_talwar]

One option is to do this

RangeSum(

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

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

)

[Anonymous · Accepted solution]

Thanks again Sunny!

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

[Anonymous]

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)

)

[sunny_talwar]

May be this

RangeSum(

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

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

)

[Anonymous]

Thanks, I had to do the following:

RangeSum(

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

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

)

Thanks again!

[sunny_talwar]

okay cool