sum of 13 previous rows

[Anonymous]

Hi,

I am trying to achieve the sum of exactly previous 13 rows for Achievement. My data is structured on weeks, quarters and fy. For eg on week 9 of any quarter i want the whole sum of previous weeks in that quarter + the previous ones from prior quarter until there are 13 weeks.

w9=sum(week9->week1 current quarter)+sum(week10->week13 previous quarter).

w10=sum(week10->week1 current quarter)+sum(week11->week13 previous quarter)

w1=sum(week1 current quarter )+sum(week2->week13 previous quarter)

Any ideas how it can be achieved?

There is also an example in the attached xls file.

Thank you for your help!

[hic]

If you have your data sorted by week, you can just use

     RangeSum(

          Achievement,

          Peek(Achievement,-1), Peek(Achievement,-2), Peek(Achievement,-3),

          Peek(Achievement,-4), Peek(Achievement,-5), Peek(Achievement,-6),

          Peek(Achievement,-7), Peek(Achievement,-8), Peek(Achievement,-9),

          Peek(Achievement,-10), Peek(Achievement,-11), Peek(Achievement,-12)

          ) as Result

HIC

[Anonymous]

Hi Hic,

Thank you for your reply.

I forgot to mention that i have to show those totals in a pivot table with weeks as a dimension and i have to make the calculations in the expressions.

Any ideas for expressions?

[hic]

If you have week as dimension and sort by this, you could use RangeSum(Above(Sum(Achievement),0,-12))

HIC

[luis_pimentel]

Hi Cristina,

What you are looking for is the "Above" function. This function allows you to access the previous record of a pivot table. Take a look at the Qlikview help for more information.

Hope that helps.

Luis.

[Anonymous]

Hi,

this will only give me the sum of weeks for each quarter it doesn't combine the quarters. for wee3 i have sum(week1->week3) but i need to show the sum of current 3 weeks + 10 previous. on week3 row i should get (week1->week3 current quarter) + (week4->week13 from previous quarter)

[Anonymous]

/yeah, i should make something like above1+above2+above3+....+above13.Would this be efficient?

[hic]

RangeSum(Above(total Sum(Achievement),0,-12))

should do the trick

HIC

[Carlos_Reyes]

As HIC stated in his answers... you can do this by two means:

Using above in a table object... Review this post:

http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/12/accumulative-sums

Using peek in the script... Review Swuehl's answer in this discussion:

Re: accumulation in script

The script option is definitely more efficient and will make your expressions simpler, although this method will also increase the script execution time.

[Anonymous · Accepted solution]

Thank you giving me the link for the review post. It was helpful.

Because i have to show only current quarter i had a few problems with where to place the Set Analysis and the formula did not work as expected when i placed it in the inner sum function. Final Formula is:

=sum({$<Quarter_no= {"$(=Max(Quarter_no))"}>}

     Aggr(

          RangeSum(  above( TOTAL sum(Achievement),0,13))

     ,Quarter_no,week_counter)

)