7 Replies Latest reply on Nov 30, 2018 2:53 AM by Nagendra Babu

# Max on a Running_Sum

I am working on a project which requires max on running sum, basic idea is:

I have a dataset as follows:

DateValue
2014.1.115
2014.1.29
2014.1.35
...
2015.1.114
2015.1.216
2015.1.38
...
2016.1.113
2016.1.212
2016.1.317
...
...
...

Step 1: obtain the value in excess of 10 with bulidin look back period (for example if I only wanted to see the data from most recent x years)

Step 2: obtain the running sum of those values for each year

Step 3: For each days, pick up the max runningsum

Ideal Output will be:

Fixed DayValue
1904.1.15
1904.1.210
1904.1.312
...

Ideal output is based on:

DateFixed DayValue in excess of 10Running Sum
2014.1.11904.1.155
2014.1.21904.1.200
2014.1.31904.1.300
...
2015.1.11904.1.144
2015.1.21904.1.2610
2015.1.31904.1.3010
...
2016.1.11904.1.133
2016.1.21904.1.225
2016.1.31904.1.3712
......
......
......
• ###### 1. Re: Max on a Running_Sum

Your requirement is not clear. Please post sample workbook along with expected results.

• ###### 2. Re: Max on a Running_Sum

I am still not clear about requirement and fixed date. But this is what I could achieve using available data.

• ###### 3. Re: Max on a Running_Sum

Day fixed basically assigned each date from the year to a lip year which provide you the possibility to compare across data on same day of the year. In this context you can ignore that. I am sorry that my company server is on 10.4, are you able to provide a more detailed answer regarding to how you figure this out? such as what is your input and what is your output.

My input here is only

Date

Value

2014.1.115
2014.1.29
2014.1.35
...
2015.1.114
2015.1.216
2015.1.38
...
2016.1.113
2016.1.212
2016.1.317
...
...
...
• ###### 4. Re: Max on a Running_Sum

Hi Lin,

Regards,

Nagendra Babu

• ###### 5. Re: Max on a Running_Sum

I have attached lower version 10.3. My input is same as yours (only 9 rows) and output is as given earlier.

• ###### 6. Re: Max on a Running_Sum

1. Calculated Running Sum first considering condition for >10

RUNNING_SUM(sum(if [Value]>10 then [Value]-10 else 0 end))

2. Added Year in a view based on available date to get results for each year

3. Get first occurrence of running some for each year.

window_min(if lookup([Value>10],0)=window_max([Value>10]) then attr([Date]) end)=lookup(attr([Date]),0)

i.e

1. Check if running sum within a year is maximum running sum value available for that year

2. Get corresponding dates and identify minimum date which is 1st occurrence.

I am not sure if this provides any clarity may be after looking at a dashboard, you can have better understanding.

• ###### 7. Re: Max on a Running_Sum

Is this what you want ?