1 2 Previous Next 15 Replies Latest reply on Jun 18, 2019 1:19 PM by Lee Polikoff

# Calculating Running Sum on Table When Only Displaying Single Month

I need to be able to calculate a running total for a given month restarting at the beginning of every year. In the image below I used a running sum. The table is filtered to only 2019. What I need to do is the following:

• Show only a SINGLE month with the running total for the year to that month. I.e. if I showed March it would have the values below for March without having any of the other months in the table. Additionally, I would want to have a table like the following with multiple months where the running sum restarts at the beginning of the year. You can see that Jan 2019 increases from Dec 2018 How can I do this?

• ###### 2. Re: Calculating Running Sum on Table When Only Displaying Single Month

Unfortunately I can't. It is all protected info.

• ###### 4. Re: Calculating Running Sum on Table When Only Displaying Single Month

So, if you select Just April, you want 171,791 in both Sales and Running sum? or you want running sum to be Jan+feb+march+april?

• ###### 5. Re: Calculating Running Sum on Table When Only Displaying Single Month

I want April total to show 143,739 and the running sum to show the sum of Jan thru April.(679,888)

• ###### 6. Re: Calculating Running Sum on Table When Only Displaying Single Month

For your 2nd question, is this what you wanted?

If so, edit your Running sum calculation to

if max(MONTH([Order Date])=1) then

SUM([Sales])

ELSE

PREVIOUS_VALUE(0)+ZN(SUM([Sales]))

END 1 of 1 people found this helpful
• ###### 7. Re: Calculating Running Sum on Table When Only Displaying Single Month

That worked great for number 2. Thanks!

• ###### 8. Re: Calculating Running Sum on Table When Only Displaying Single Month

Lee Polikoff this post in the community forum seem to be exactly what you want. See if you can replicate it. and let me know if you get stuck somewhere. If this answers your question, kindly mark it answered for others to search for solution.

• ###### 9. Re: Calculating Running Sum on Table When Only Displaying Single Month

Rup Joshi This sort of works. I tried this first but it wouldn't compile

{ FIXED : sum(if [Actl Pckg Rls Date]>=DATETRUNC('year',WINDOW_MAX([Actl Pckg Rls Date])) and [Actl Pckg Rls Date]<=WINDOW_MAX([Actl Pckg Rls Date])
then [On-TimeCount] else 0 end )}

I then tried:

{ FIXED : sum(if [Actl Pckg Rls Date]>=DATETRUNC('year',[Enter End Date]) and
[Actl Pckg Rls Date]<=[Enter End Date] then [On-TimeCount] else 0 end )}

This sort of works. What I need to do is tie the filter to the parameter. This way when the filter changes, the parameter value changes (or the other way around).

• ###### 10. Re: Calculating Running Sum on Table When Only Displaying Single Month

Did you try the solution by Joe - the one that is marked correct answer?Joe Oppelt (last 2nd post in the above link)

• ###### 11. Re: Calculating Running Sum on Table When Only Displaying Single Month

Rup Joshi - Ok, that got me 99% to the solution. The one thing I can't get to is the measure names to the left of the rows. I've tried dragging "Measure Names" over to Rows, but it shows "No Measure Value" • ###### 12. Re: Calculating Running Sum on Table When Only Displaying Single Month

That's great! can you share your twbx?

• ###### 13. Re: Calculating Running Sum on Table When Only Displaying Single Month

Well I can't BUT the workbook that you pointed me to with the solution I used has the same issue. I've attached that one.

• ###### 14. Re: Calculating Running Sum on Table When Only Displaying Single Month

Oh, ok. Remove measure names from rows. Try to drag sum(sales) to rows. Click show me and change from graph to text table. Hope this helps. Somehow the % value changes, so I removed it. Not sure if you need the % value 1 of 1 people found this helpful
1 2 Previous Next