Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

paulyeo11
Valued Contributor II

1,730,509 / 4,884,817 = 0.354 But QV Return 5.01

Hi All

I have a QV Doc

it return wrong cal value.

Hope some one can advise me where i go wrong ?

Paul

1 Solution

Accepted Solutions

Re: 1,730,509 / 4,884,817 = 0.354 But QV Return 5.01

For your COGS_INT expression, you were using Full Accumulation method, but unfortunately when you use Column(1)/Column(2), it doesn't take the accumulated values, but the original un-accumulated values. A work around is to use no accumulation with RangeSum(Above()) expression:

If(sum(STK_CLOSE) > 0 and sum(STK_OPEN) > 0, RangeSum(Above(Sum({$<year = {$(=max(year))}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} (COGS+COGS_INT_)/1), 0, RowNo())))

Once you that the Column(1)/Column(2) calculation is fixed.

Capture.PNG

4 Replies

Re: 1,730,509 / 4,884,817 = 0.354 But QV Return 5.01

For your COGS_INT expression, you were using Full Accumulation method, but unfortunately when you use Column(1)/Column(2), it doesn't take the accumulated values, but the original un-accumulated values. A work around is to use no accumulation with RangeSum(Above()) expression:

If(sum(STK_CLOSE) > 0 and sum(STK_OPEN) > 0, RangeSum(Above(Sum({$<year = {$(=max(year))}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} (COGS+COGS_INT_)/1), 0, RowNo())))

Once you that the Column(1)/Column(2) calculation is fixed.

Capture.PNG

paulyeo11
Valued Contributor II

Re: 1,730,509 / 4,884,817 = 0.354 But QV Return 5.01

Hi Sunny

Thank you very much for your help.

Paul

Re: 1,730,509 / 4,884,817 = 0.354 But QV Return 5.01

Not a problem. I am glad I have been helpful

paulyeo11
Valued Contributor II

Re: 1,730,509 / 4,884,817 = 0.354 But QV Return 5.01

Hi Sunny

I have another question related to compute the result i need :-

Expression for create each row 30 count total 12 row 360 count ?

Community Browser