7 Replies Latest reply on Feb 1, 2019 12:32 AM by Simon Runc

# How to make Promo Period chart: multi-year, days from date, running total

I pretty much have no idea what to do to make this (I've researched and tried lots of things but nothing works right). I need to make a line chart that looks something like this:

Each year there is a promo period. The start and end dates vary. I want to show on the x-axis the number of days from the end of the promo with a running total sales \$ for each year. Each year has a different length of days for the promo period so the start of the line will differ. I've attached a workbook with fake data (and a lot less) in a spreadsheet. (I normally connect to a database and have millions of rows)

One thing to note is there are sales after the promo period of that year that shouldn't be included. Here are the promo end dates:

2017  3/8/2017

2018  3/1/2018

2019 3/6/2019

EDIT:

The first date of that year will always be in that year's promo window. I made a calculation just taking 2017 into account (filtered to only 2017)

DATEDIFF("day", Date([Datetime]), #2017-03-08#) and I think that part works and I made the following chart:

However, I can't seem to reverse the running total. I've looked it up and nothing seems to work correctly. I replaced w/ the updated workbook.

I'd appreciate any help or any recommended resources! I'm using Desktop 2018.3.

• ###### 1. Re: How to make Promo Period chart: multi-year, days from date, running total

hi Caleb,

So the key to this is to create a common baseline for each year. I've broken this down into stages, but you can nest this all in one (or 2) calculations if you wish.

First we need to get the First Date for each year. We can do this with an LoD expression

[First Date of Each Years Promos]

{FIXED [Year]: MIN([Datetime])}

Then we can use this to work out how many days from this start each day is...

[Promo Days]

DATEDIFF('day',[First Date of Each Years Promos],[Datetime])

We can now use this as our common baseline (so for all years Promo Days = 0 is the first day, and so on.

Finally we can plot this on a line chart, and use the "quick table calculation" option to get a running sum

Hope that is what you were after and all makes sense.

btw if you want to filter out certain dates, just filter them out and everything should update to reflect what is unfiltered (unless you are removing dates which would be the start, in that case you'd need to also add the date filter to be "in context" so it affects out first LoD, which gets the first date)

2 of 2 people found this helpful
• ###### 2. Re: How to make Promo Period chart: multi-year, days from date, running total

Good morning

see the attached

the start and end dates are hard coded

then used in filters set to true

and the promo days are  - convert to discrete dimension

and make the viz

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

2 of 2 people found this helpful
• ###### 3. Re: How to make Promo Period chart: multi-year, days from date, running total

Hi Simon Runc,

Thanks for your reply! I'm going to check it out. But for some reason I can't open the packaged workbook you've attached. Error(160,66): no declaration found for element 'simple-id' and so on. I have Desktop 2018.3.0 so not sure if that's the issue.

• ###### 4. Re: How to make Promo Period chart: multi-year, days from date, running total

oops my bad! I accidentally did it on the 2019 Beta!

Here you go...in an actually released version!!

3 of 3 people found this helpful
• ###### 5. Re: How to make Promo Period chart: multi-year, days from date, running total

hi Vanessa,

Glad it helped. With regards your second question, if you change the LoD expression from a MIN to a MAX it will now ensure that the promos end on the same date, rather than start on the same date (I've renamed the field from First Date... to Last Date...

[Last Date of Each Years Promos]

{FIXED [Year]: MAX([Datetime])}

There are other ways you can specify the running sum to work in reverse, but will this does the trick, so no need to complicate things!

On your first question ...In Tableau (like many things) there are lots of ways to achieve the same thing, and which is best depends on the situation (and sometimes no one way is "better"). I guess mine has the advantage of being more dynamic, in that the last promo date is picked up in the formula, but as you have to hard-type the Promo End Date anyway,  Jim's will be more efficient as mine has to perform an LoD calculation, which has some computational cost. You won't really tell the difference unless you get into the 10s and 100s of millions of rows of data.

I've attached an updated version and put a formula in to filter out the non-needed dates (which I did have to make a "context filter" so it affects the {FIXED [Year]: MAX([Datetime])} formula

1 of 1 people found this helpful
• ###### 6. Re: How to make Promo Period chart: multi-year, days from date, running total

Thanks Simon Runc! Since I can't reverse the running total, I'm trying to figure out how to make it look like what the bosses want. Is there a way in Tableau to edit the axis numbers so the negative sign isn't showing? Or maybe I need to make a calculated field to find the reversed running total rather than using the table calculation?

EDIT:

I figured it out I think. I right clicked the axis, chose format, then changed the number format to custom #,##0;#,##0

It takes away the negative sign.

• ###### 7. Re: How to make Promo Period chart: multi-year, days from date, running total

Ah those pesky bosses and their wants!!

Cool. Yes that's the way I'd suggest. We can specify the Running Sum to run in the other direction, but why work harder!

In fact you could go with a custom formatting of

"";#,##0;#,##0

which would remove the positive numbers

1 of 1 people found this helpful