Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mahitham
Contributor

Round() help

Hi Experts,

Can any one please help me to convert below oracle decode function into Qlik UI as expression.

round( Sum ( decode ( [name] , 'valuerisk', [Value] ,0 ) ) /1000, 2 )

Here name and Value are the fields.

jontydkpi  could you please help me.

Thnaks in advance.

1 Solution

Accepted Solutions
MVP
MVP

Re: Round() help

I think this is what you are looking for:

Round( Sum( If( [name] = 'valuerisk' , [Value] , 0 ) ) / 1000 , 0.01 )

You might have to add Num for the display:

Num( Round( Sum( If( [name] = 'valuerisk' , [Value] , 0 ) ) / 1000 , 0.01 ) , '0.00' )

6 Replies
MVP
MVP

Re: Round() help

I think this is what you are looking for:

Round( Sum( If( [name] = 'valuerisk' , [Value] , 0 ) ) / 1000 , 0.01 )

You might have to add Num for the display:

Num( Round( Sum( If( [name] = 'valuerisk' , [Value] , 0 ) ) / 1000 , 0.01 ) , '0.00' )

mahitham
Contributor

Re: Round() help

Hi Petter,

Thanks a lot for your reply. Its working fine. Some expression contains like below.

Could you please help me to convert this || 'Y' as well. Cant create new thread again

round( Sum ( decode ( [name] , 'valuerisk' || 'Y', [Value] ,0 ) ) /1000, 2 )

MVP
MVP

Re: Round() help

|| is Oracle string concatenation operator for joining two strings together.

So it would be the same as:

Round( Sum( If( [name] = 'valuerisk' || 'Y' , [Value] , 0 ) ) / 1000 , 0.01 )

That should be exactly the same as:

Round( Sum( If( [name] = 'valueriskY' , [Value] , 0 ) ) / 1000 , 0.01 )



mahitham
Contributor

Re: Round() help

Thanks petter

Num( Round( Sum( If( [name] = 'valuerisk' , [Value] , 0 ) ) / 1000 , 0.01 ) , '0.00' ) THIS EXPRESSION is working fine in UI

but when I have moved the same expression to backend script its showing invalid expression. Actually to apply group by logic I have to move to script only there is 50 expressions like this.


could you please help me on this.

MVP
MVP

Re: Round() help

I think Petter has covered this perfectly

MVP
MVP

Re: Round() help

In SQL (and in Qlik), when you include an aggregation function like Sum(), all other fields not in some aggregation need to be in the Group By. That is how it is.

Community Browser