Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rsingh43
Contributor

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.

1 Solution

Accepted Solutions
rsingh43
Contributor

Re: Case when..?

Thanks again Sunny!

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

7 Replies
agigliotti
Honored Contributor II

Re: Case when..?

in your script you can do as below:

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


I hope it helps.

MVP
MVP

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)

)

rsingh43
Contributor

Re: Case when..?

Thanks again Sunny!

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

rsingh43
Contributor

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)

)

MVP
MVP

Re: Case when..?

May be this

RangeSum(

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

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

)

rsingh43
Contributor

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!

MVP
MVP

Re: Case when..?

okay cool