Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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]))),
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
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
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
is this rangesum(gross,-amount) or
rangesum(gross-amount)
@hic beacuse in SQL i minus between gross and amount
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