topic Expression is not working as per expectation. in QlikView

Expression is not working as per expectation.

parthesh — Thu, 08 Feb 2018 09:45:27 GMT

Hello all,

i have one expression in which i am trying to find percentage as per below.

((Sum({< REC_TYPE={InvoiceDateRec}>}if(ARCOST_TOTAL_COST>=0 or ARCOST_TOTAL_COST<=0,(RAGLDA_ACCTD_AMOUNT*XRATE_CONST*RATE)))

Sum({< REC_TYPE={InvoiceDateRec}>} if(ARCOST_TOTAL_COST>=0 or ARCOST_TOTAL_COST<=0,(ARCOST_TOTAL_COST*XRATE_CONST*RATE))))

(Sum({< REC_TYPE={InvoiceDateRec}>} (RAGLDA_ACCTD_AMOUNT*XRATE_CONST*RATE))))

*100

Basically it is ((sales - cost) / sales)*100 .

here when i put whole above expression it is showing wrong result. but when i create seperate column for (sales-cost). and one more column for (sales) and calculating final percentage column using "column()" like column(1)/column(2) it is showing correct result.

thanks in advance.

Re: Expression is not working as per expectation.

marcus_sommer — Thu, 08 Feb 2018 12:12:50 GMT

Maybe this made a difference:

(rangesum(sales, -cost) / sales)*100

- Marcus

Re: Expression is not working as per expectation.

sunny_talwar — Thu, 08 Feb 2018 12:45:07 GMT

Why do you need the if statement here? If ARCOST_TOTAL_COST is a number, then it will either be greater than equal 0 or less than equal to 0... are you checking if it is text???

Also, it should not matter, but Sales in the numerator have a different expression than sales in the denominator??

Re: Expression is not working as per expectation.

parthesh — Thu, 08 Feb 2018 14:28:44 GMT

Hello Sunny,

to avoid null we are doing that.

Re: Expression is not working as per expectation.

sunny_talwar — Thu, 08 Feb 2018 14:29:33 GMT

Nulls can be avoided like this in set analysis

ARCOST_TOTAL_COST = {"*"}