1 2 Previous Next 16 Replies Latest reply on Jun 28, 2017 8:47 AM by john gu

# Running total ending at current month but continuing in previous months

Hello forum!

I have a running total calculated field which stops a cumulative line in the current month so that it doesn't continue into the future (I am comparing this year with previous years). However, the problem with my calculated field is that if there is a month with no records the line breaks. Does anyone have an idea on how I would fix this please?

IF  sum([Number of Records]) = 0

OR  isnull(sum([Number of Records]))

THEN NULL

ELSE running_sum(sum([Number of Records]))

END

Here is a screenshot of what is happening:

• ###### 1. Re: Running total ending at current month but continuing in previous months

Hi Lucie,

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

• ###### 2. Re: Running total ending at current month but continuing in previous months

Thanks Norbert, the problem with this approach is that when you have a scale which extends into the future (see my screenshot where I have months in the future) the line will continue and I need to stop it at the present day. Does that make sense? Thanks for your thoughts!

• ###### 3. Re: Running total ending at current month but continuing in previous months

Hi Lucie,

That makes sense;) but at this moment "can't get my head around" this one.

We will ask Simon Runc. Not able to recall the "trick" on this one.

Could you help Lucie with her challenge.

Upfront thanks a TON for your prompt assistance

• ###### 4. Re: Running total ending at current month but continuing in previous months

Thanks for the ping Norbert...interesting problem

hi Lucie,

So one way is to use a "fake" reference line to force the "continuous" axis out to the last day of data, but truncate the Running Sum at today's month (I assume the issue with using a continuous month/date is that it truncates at the last "plotted" data-point)

So first I created the RUNNING_SUM formula, which returns NULL after the current month

[Running Sum of Sales]

IF DATETRUNC('month', today()) >= DATETRUNC('month', ATTR([Date]))

THEN RUNNING_SUM(SUM([Sales]))

END

and this works as expected...but now we want the Axis to go to the last date...

So I created an LoD to bring back the last date in the data

[Max Date]

{MAX([Date])}

I bring this onto the detail shelf, so I have access to it as a reference line. I then set up a reference line on this measure

I've left the line shown, so you can see what's going on, but in your final version just set the line style to none.

Hope that does what you need (and makes sense) but let me know if not.

• ###### 5. Re: Running total ending at current month but continuing in previous months

Hello Simon

I really appreciate the help (as usual!), I have mocked up an example file for you as this didn't solve my problem and I imagine it is due to the way I am working with the date field. I have been asked to look at a cumulative value but YoY so for that I have reduced the dates down to the months. For earlier years, I want to show the full 12 month line but for this year just up until the latest date available i.e. not continuing.

Hopefully that is clear.

Sorry for the extra difficulties, this was the only way I could work out how to do the YoY comparison!

Thanks, Lucie

• ###### 6. Re: Running total ending at current month but continuing in previous months

hi Lucy,

OK I think I see...so I'm going to work with your existing logic on how you've set up your years (FY 14/15, F 15/16...etc.) and in particular the [New cycles] calculated dimension, which is why this solution is a little intricate!

So first thing I did was to create some calculations so I could get the 1st and last month of the data for the last year (defined at the last [New cycles])

We start off nice and easy...

[Max Month of Last Year]

DATEPART('month',{MAX([Date Created])})

now it get's a little more complicated to get the first month (in this example March, or Month 3)

[Min Month of Last Year]

DATEPART('month',{MIN(IIF([New cycles]={MAX(IIF([New cycles]<>'Previous Years',[New cycles],NULL))},[Date Created],NULL))})

So what's this doing...Let's start in to out.

First I need to just get the last year....so I tried {MAX([[New cycles]])} thinking that due to the numbering "FY 14/15", "FY 15/16" the MAX, alphabetically would be the last year...but no, alphabetically "Previous Years" is last...so we add an extra IIF condition to not consider "Previous Years"...this is what that bit does {MAX(IIF([New cycles]<>'Previous Years',[New cycles],NULL))}

Next we want to check where [New cycles] = the Last [New Cycles]...and then take the MIN month of that. This is what that bit does {MIN(IIF([New cycles]={MAX(IIF([New cycles]<>'Previous Years',[New cycles],NULL))},[Date Created],NULL))}. And then we take the Month from this.

Phew...Once we have this we can build a simple filter

[Filter Out non comparable Month]

DATEPART('month',[Date Created])>=[Min Month of Last Year]

AND

DATEPART('month',[Date Created])<=[Max Month of Last Year]

and I bring this onto the filter shelf...let me know if you want to see the Axis extend to all months (if so we can embed the filter into the Running Sum, rather than just filter out all the data).

A few other things I added/changed

[Month Name]

DATENAME('month',[Date Created])

No need to have a 12 stage IF...as we can just use DATENAME

[Month]

DATEPART('month',[Date Created])

same as above, but brings back 1,2,3... rather than Jan, Feb, Mar...so I can sort the months (as Strings the [Month Name] gets sorted alphabetically)

Hope that is what you need, and makes (some) sense.

• ###### 7. Re: Running total ending at current month but continuing in previous months

Hello Simon,

I am awfully sorry but yes I will need to see the months where there is no data in the current cycle too. Is that possible?

Thanks

• ###### 8. Re: Running total ending at current month but continuing in previous months

Yes very much so!

Tableau (in fact, no one and nothing!), can plot NULLs...which means we can either filter out the data (as we did in the previous version) or embed the filter in the calculation so that for the months we don't want data plotted, just equate to NULL.

So I've just added this calculation in and used this one...

[Running Sum - Only Comparable Months]

IF MIN([Filter Out noncomparable Month]) THEN [Running Sum] END

So if the Months are within our (comparable) data-range it does the running_sum, else NULL (as there is no ELSE statement, NULL is the default). The MIN wrapper is just so it's aggregated, and as we have month in the Viz LoD it all works fine.

• ###### 9. Re: Running total ending at current month but continuing in previous months

Hi Simon,

Are you saying that I cannot achieve the below where I have a line for the months of Dec-Feb for previous cycles? In your file, there are no values for the previous years even though there are technically values (even if not for the current year). Thanks in advance,

• ###### 10. Re: Running total ending at current month but continuing in previous months

Hello Simon,

I thought I would share a better example of the file from before where I have gaps, I need to show all months and I want to show the previous years data even if there is no comparable data in the current cycle.

Thanks!

• ###### 11. Re: Running total ending at current month but continuing in previous months

Thanks for that Lucie...so I think we can actually just do this with a single setting!!...after all the LoDing!!

I've just formatted the measure (running sum) to connect NULLs

4 of 4 people found this helpful
• ###### 12. Re: Running total ending at current month but continuing in previous months

Wow, that is brilliant. Is it a new feature or has it always been there? Whatever the answer, I am so happy! Thanks a million

• ###### 13. Re: Running total ending at current month but continuing in previous months

Excellent....As far as I know (my first usage was T8) it's always been there!!...I look at a lot of ratio stuff (in which there is always an entry, or 2, which equate to NULL), so use this quite a bit (it can be a little dangerous, as you no longer get the "there are 8 NULL values" warning!...so data errors can go un-noticed).

...still we learned a bit about LoDs!!, which might come in handy on the next project!!

• ###### 14. Re: Running total ending at current month but continuing in previous months

Hi, Simon. I am having same issue that for running total there are null month in the middle. in line chart, there is broken point in that month. In bar chart, there isn't a bar for that month.

Is there a way to show?

Thanks.

John

calculated field is

IF DATETRUNC('month', today()) >= DATETRUNC('month', ATTR([P Date]))

THEN RUNNING_SUM(SUM([LeftV]))

END

1 2 Previous Next