2 Replies Latest reply on Dec 19, 2016 2:48 PM by Alban Spella-Barberet

# How to calculate an accumulated variation?

Hello,

I have a simple dataset:

two cities and a count during several weeks. I also have variations from a week to another week.

I am trying to display in a way I can trend an accumulated variation per week.

A would show:

0,0,0,1,3,3,3,167,204

B would whow:

0,236,236,237,237,237,271,262.

One idea is to display the count minus the count of the oldest week on a calculated field however I am not sure how to do this, basically:

New count= Count - count of week 21/10/2016 (or oldest week).

 City Count VARIATION ENDING_WEEK A 1057 2 21/10/2016 A 1057 0 28/10/2016 A 1057 0 4/11/2016 A 1058 1 11/11/2016 A 1060 2 18/11/2016 A 1060 0 25/11/2016 A 942 0 2/12/2016 A 1107 165 9/12/2016 A 1143 36 16/12/2016 B 511 0 21/10/2016 B 747 236 28/10/2016 B 747 0 4/11/2016 B 748 1 11/11/2016 B 748 0 18/11/2016 B 748 0 25/11/2016 B 782 34 9/12/2016 B 773 -9 16/12/2016
• ###### 1. Re: How to calculate an accumulated variation?

Hi Alban,

Find my approach as reference below and stored in attached workbook version 9.3

a. Oldest week per City: {fixed [City]:min([Ending Week])}

b. Variation Cleaned: if [Oldest week]=[Ending Week] then 0 else [Variation] END

c. Variation cumm.: sum([Variation cleaned])+PREVIOUS_VALUE(0)

PS there is an issue in your data B, 2-12-2016, can't solve it with IFNULL, ZN etc;)

1 of 1 people found this helpful
• ###### 2. Re: How to calculate an accumulated variation?

Exactely what I need!

Beauty.

Thanks a lot, learning everyday.