8 Replies Latest reply on Oct 9, 2018 4:20 AM by oscar vicente

# Cut-off Running Sum Mid-Year

Hello. I have a nut that has turned out to be surprisingly hard to crack.  See the attached packaged workbook to see the issue.  I'm putting together a budget dashboard.  In one of the views (second tab of workbook), I want to show a running sum of actuals against a running sum of budget, and show the variance between the two.  The problem is that my actuals stop at April, but the budget continues through the rest of the year.  So when I put together a running sum the actuals flatline at April, the budget continues to rise, and the variance grows rapidly.  I want both the actuals line and the variance bars to end at april.  When I try to put together a calculated field (like the one with the exclamation point in the workbook), I get the error that I cannot mix aggregate and non-aggregate results in an if statement.

Any help with this would be much appreciated.

• ###### 1. Re: Cut-off Running Sum Mid-Year

Hi Jmogielnicki, I think the attached workbook works. Instead of going after the date, I wrote calcs to test for SUM([amount])>0, which seems to produce the chart you want. I wrote two new calcs:

Actual (blue line):

IIF(SUM([Amount])>0,RUNNING_SUM(SUM([Amount])), NULL)

Differential (grey bars):

IIF(SUM([Amount])>0,[Actuals minus Budget], NULL)

Here's the chart: Hope this is what you were looking for.

--Shawn

10 of 10 people found this helpful
• ###### 2. Re: Cut-off Running Sum Mid-Year

Hey Shawn, that is super helpful, and definitely fixed one of the two issues.  Was still having trouble getting the running sum of the variance to cut itself off but fixed that with a calculation that referenced one of the calcs that you set up:

IIF(isnull([Actuals minus Budget]), NULL,

RUNNING_SUM(sum((if [Type] = "Actuals" then [Amount] else null end)))-

RUNNING_SUM(sum((if [Type] = "Budget" then [Amount] else null end))))

I'll attach the workbook in case anyone else comes around with a similar issue.

Thanks for the help.

• ###### 3. Re: Cut-off Running Sum Mid-Year

This solution works well if [Amount] is not NULL, but when it is, the running sum will not display, causing discontinuities in the line. For example, if my line is showing cumulative increases by day, and no records are added on weekends, then the line is broken into segments.

I could roll up to by-week instead of by-day, but 'week 1', 'week 2', etc is not as desirable as actual dates.

• ###### 4. Re: Cut-off Running Sum Mid-Year

I have the same problem, I have discontinuities in the lines. Any idea to solve it?

• ###### 5. Re: Cut-off Running Sum Mid-Year

You can try this, Just replace "Actual".

IIF(SUM([Actual])>0,RUNNING_SUM(SUM([Actual])), (SUM([Actual])+ PREVIOUS_VALUE(0)))

• ###### 6. Re: Cut-off Running Sum Mid-Year

In a similar case, i used the LAST function to stop showing the running sum after current date. Here goes my calculated field -

IF LAST()>=(DATEDIFF('week',TODAY(),[End Date],'Monday'))

THEN RUNNING_SUM(SUM([Amount]))

ELSE NULL

END

• ###### 7. Re: Cut-off Running Sum Mid-Year

Hi,

This might be too late for you, but maybe someone else has the same problem and finds it here. I found this article that solved it for me:

Regards,
Willem

1 of 1 people found this helpful