Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problem with conditional & alt result

Hi

I have this formula in one pivot table graph however the last result isn't giving me what I need.

alt(if((FABS(Sum ([UN DEVUELTAS]))/(Sum ([UN VENDIDAS])+Sum ([UN CANCELADAS])))>2,2,fabs(Sum ([UN DEVUELTAS]))/(Sum ([UN VENDIDAS])+Sum ([UN CANCELADAS]))),"X")

the basic formula is this: Sum ([UN DEVUELTAS])/(Sum ([UN VENDIDAS])+Sum ([UN CANCELADAS])

if this formula give me a result greater than 2, it should report 2

and if this formula is undetermined (9/0), it should report 1

if i use the formula  with 0 as "X", the original formula and the  first conditional works however the second conditional doesn't work and the others columns of the pivot table works also,

if i use the formula  with 1 as "X", nothing works (the original formula, the  first conditional & the second conditional & all the other columns of the pivot table, i don't know why the are affected by the result of this column)


what should I change???

the script of my table is like this:

VENTAS:

LOAD

     [FECHA DE FACTURA],

     CODIGO,

     [UN VENDIDAS],

     [UN CANCELADAS],

     [UN DEVUELTAS],

FROM

(ooxml, embedded labels, header is 5 lines, table is HBRIEF_7534);


thanks for your help

regards,

1 Solution

Accepted Solutions
marcus_sommer

Try this:

if(FABS(Sum ([UN DEVUELTAS]))/(Sum ([UN VENDIDAS])+Sum ([UN CANCELADAS]))>2,

2,

alt(fabs(Sum ([UN DEVUELTAS]))/(Sum([UN VENDIDAS])+Sum ([UN CANCELADAS])),1))

- Marcus

View solution in original post

2 Replies
marcus_sommer

Try this:

if(FABS(Sum ([UN DEVUELTAS]))/(Sum ([UN VENDIDAS])+Sum ([UN CANCELADAS]))>2,

2,

alt(fabs(Sum ([UN DEVUELTAS]))/(Sum([UN VENDIDAS])+Sum ([UN CANCELADAS])),1))

- Marcus

Not applicable
Author

Thanks for your help,.,.,that worked perfectly

Regards,

Peter