decode in qliksense

[capriconuser]

hi 

how i use decode in qliksense .. this is the expression 

SUM(DECODE(Rcount,0,DECODE(H.documentno,NULL,(C.gross- C.amont),0),0))

how to use decode in qliksense ?

any help 

[capriconuser]

i want tio use this in chart expression not in script

is this possible to combine both statement and remove plus sign  in expression 

sum({<[Year] = {"$(=vYear)"},[Rcount ] = {0},[Table1.Flag] = {'USA'},[Table2.Flag] = {'USA'}, documentno={"*"} >} (gross - amount))
+
sum({<[Year] = {"$(=vYear)"},[Rcount ] -= {0},[Table1.Flag] = {'USA'},[Table2.Flag] = {'USA'}>} (gross - amount))

is there  any logic @JuanGerardo 

[JuanGerardo]

Yes, you can use intersection and union of both data sets:

sum({
<[Year] = {"$(=vYear)"},[Table1.Flag] = {'USA'},[Table2.Flag] = {'USA'}>
*
(<[Rcount ] = {0}, documentno={"*"} > + <[Rcount ] -= {0}>)
} (gross - amount))

JG

[capriconuser]

why u use gross-amount 1 time .. whereas in SQL expression this is being used twice.. will u please explain code 

and also this expression shows an error .. error : error is in set modifier expression

I am trying this but I think this does not meet this condition 

SUM(DECODE(Rcount,0,DECODE(documentno,NULL,0,(gross - amount)),(gross - amount)))

 sum({<[Year] = {"$(=vYear)"},
 [Rcount ] = {">0"},[Table1.Flag] = {'USA'},
 [table2.Flag]  = {'USA'} >}
 if([Rcount ] = 0,
 if(isNull([documentno]),0,
([GROSS]- [AMOUNT])),
([GROSS]- [AMOUNT]))),

@JuanGerardo 

[hic]

Your initial expression
   SUM(DECODE(Rcount,0,DECODE(H.documentno,NULL,(C.gross- C.amont),0),0))
basically says the sum of "IF Rcount=0 and H.documentno is NULL THEN (C.gross- C.amont) ELSE 0". Right?

That can be written as
   Sum(If(Rcount=0 and IsNull(H.documentno),RangeSum(C.gross,-C.amont),0))
The RangeSum() is needed if C.gross or C.amont can be NULL.

Then you change places of (Gross-Amount) and 0. I assume that the latter is what you want. Then you could write
   Sum(If(Rcount<>0 and not IsNull(H.documentno),RangeSum(C.gross,-C.amont),0))

Introducing set analysis can cause problems:
* You cannot test for NULL in a set expression.
* Numeric selections need to match the format used.
* Unions between conditions can be tricky

But the following could work:

sum (
   {<
   [Year] = {"$(vYear)"},
   [Rcount] -= {">=0<=0"}, // A format independent search that excludes 0
   [Table1.Flag] = {'USA'},
   [table2.Flag] = {'USA'}
   >
   + // The union between the two set modifiers
   <
   [Year] = {"$(vYear)"},
   [documentno] = {"*"}, // A search for ANY value, that excludes NULL
   [Table1.Flag] = {'USA'},
   [table2.Flag] = {'USA'}
   >}
   RangeSum(GROSS,-AMOUNT)
)

HIC

[capriconuser]

i need expression for both below codes

for this

SUM(DECODE(Rcount,0,DECODE(documentno,NULL,(gross- amount),0),0))

and also for this

SUM(DECODE(Rcount,0,DECODE(documentno,NULL,0,(gross-amount)),(gross- amount)))

so for first SQL statement  try this 

sum({<[Year] = {"$(=vYear)"},[Rcount ] = {">0"},[Table1.Flag] = {'USA'},[Table2.Flag] = {'USA'} >}if( Rcount = 0,if( isNull(H.documentno), C.gross - C.amont, 0) ,0))

and for 2nd statement I try this 

 sum({<[Year] = {"$(=vYear)"},[Rcount ] = {">0"},[Table1.Flag] = {'USA'},[Table2.Flag] = {'USA'} >} if(Rcount = 0, if(isNull([documentno]),0,
(gross- amount)),(gross- amount)),0)

but this shows an error

@hic  so your sense expression is for which statement 

[hic]

If you want them as two statements, the first one has the following logic

and I would use

sum (
   {<
   [Rcount] = {">=0<=0"} // A format independent search that matches 0
   >}
   If(IsNull(documentno),RangeSum(gross,-amount))
)

and the second has the following logic

sum (
   {<
   [Rcount] -= {">=0<=0"} // A format independent search that excludes 0
   >
   + // The union between the two set modifiers
   <
   [documentno] = {"*"} // A search for ANY value, that excludes NULL
   >}
   RangeSum(gross,-amount)
)

In addition you can have other conditions in your set expression, but I haven't included them since you don't have them in the original expression.

HIC

[capriconuser]

is this   rangesum(gross,-amount) or 

rangesum(gross-amount)

 @hic beacuse in SQL i minus between gross and amount

[hic]

Rangesum(gross, -amount)

You could use "gross-amount", but then there is a risk of incorrect calculations. If one of the two is NULL, Rangesum() will treat this as zero, whereas a normal addition/subtraction will return NULL:

10 - NULL    =>     NULL

Rangesum(10, NULL)  =>  10