topic Re: Cumulative calculation from 3rd day in QlikView

Cumulative calculation from 3rd day

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

We were using the below logic to get the cumulative total.

Logic: rangesum(above(total sum(Product$TY), 0, rowno(Total)))

But we need to calculate the cumulative total from 3rd row.

Thanks..

Re: Cumulative calculation from 3rd day

sujeetsingh — Wed, 15 Jun 2016 04:17:54 GMT

So this expression is used in straight table. You can use rank() there and just write a if condition that if rank is 3 then rangesum(above(total sum(Product$TY), 0, rowno(Total)))

Re: Cumulative calculation from 3rd day

nareshthavidishetty — Wed, 15 Jun 2016 04:43:22 GMT

Hi,

Can you please provide the complete logic,because when i include the rank in the logic it gives error.

Thanks..

Re: Cumulative calculation from 3rd day

MayilVahanan — Wed, 15 Jun 2016 04:48:02 GMT

Are you looking like this

rangesum(above(total sum(Amount), 0, rowno(Total)-2))

Re: Cumulative calculation from 3rd day

tamilarasu — Wed, 15 Jun 2016 04:56:25 GMT

Hi,

Try this,

Rangesum(Above(total If(Rowno(Total)>2, sum(Product$TY)), 0, Rowno(Total)))

Re: Cumulative calculation from 3rd day

nareshthavidishetty — Wed, 15 Jun 2016 05:29:10 GMT

Hi,

We need to accumulate the data as below..

31st would remain same..1st would remain same and from 2nd it should accumulate with 1st and so on..

Thanks..

Re: Cumulative calculation from 3rd day

MayilVahanan — Wed, 15 Jun 2016 05:44:49 GMT

Try like this

RangeMax(If(Rowno(Total)> 2, rangesum(above(total sum(Amount), 0, rowno(Total)-1)), 0), Sum(Amount))

Re: Cumulative calculation from 3rd day

nareshthavidishetty — Wed, 15 Jun 2016 06:04:40 GMT

Thanks Mayil..

The same if we need to do it for %..and we use the below logic..but it simply adds the sum like 1st the % is 10.5% on 2nd its 90.5% but here the cumulative gives 101% which is not correct..we need the avg %

Below is the logic used:

RangeMax(If(Rowno(Total)> 2, rangesum(above(total ((Sum(Product$TY))/Sum(Product$LY)), 0, rowno(Total)-1)), 0), ((Sum(Product$TY))/Sum(Product$LY)))

Thanks...

Re: Cumulative calculation from 3rd day

MayilVahanan — Wed, 15 Jun 2016 06:08:26 GMT

Instead of RangeSum, try with RangeAvg() function.

Hope it helps