-
1. Re: How to make Promo Period chart: multi-year, days from date, running total
Simon RuncJan 30, 2019 10:49 AM (in response to Caleb Bross)
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)
-
PromoAnalysis_SR.twbx 74.6 KB
-
-
2. Re: How to make Promo Period chart: multi-year, days from date, running total
Jim DehnerJan 30, 2019 11:10 AM (in response to Caleb Bross)
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.
-
PromoAnalysis_v2018.2.twbx 22.4 KB
-
-
3. Re: How to make Promo Period chart: multi-year, days from date, running total
Caleb Bross Jan 31, 2019 6:20 AM (in response to Simon Runc)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
Simon RuncJan 31, 2019 6:44 AM (in response to Caleb Bross)
oops my bad! I accidentally did it on the 2019 Beta!
Here you go...in an actually released version!!
-
PromoAnalysis_SR_v2018.3.twbx 74.5 KB
-
-
5. Re: How to make Promo Period chart: multi-year, days from date, running total
Simon RuncJan 31, 2019 8:16 AM (in response to Caleb Bross)
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
-
PromoAnalysis_SR_v2018.3.twbx 79.7 KB
-
-
6. Re: How to make Promo Period chart: multi-year, days from date, running total
Caleb Bross Jan 31, 2019 9:05 AM (in response to Simon Runc)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
Simon RuncFeb 1, 2019 12:32 AM (in response to Caleb Bross)
1 of 1 people found this helpfulAh 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