December 15, 2015
Tableau Tip Tuesday: How to Calculate Week over Week Change of the Last Complete Week
For this week's tip, I look back at a question that Hashu Shenkar, one of my former Data Schoolers, asked while he was working with me:How do I calculate the week over week change for only completed weeks? And how to I show only value for the latest complete week.This required several table calculations, and I'm pretty certain that there's a better, more efficient way to calculate this with LOD calcs. However, I couldn't figure out how to get the LOD calc to work, so I got the result the way I would have before LOD calcs existed.
If you know a better way to accomplish this, please leave a comment. Enjoy!
Subscribe to:
Post Comments
(
Atom
)
I've done this using at set on WEEK(date) where the count of DAY(date) is seven.
ReplyDeleteAh yes! Excellent idea! I always forget about using sets this way.
DeleteCan you please give us a tutorial about set someday?
ReplyDeleteHi Andy,
ReplyDeleteI'm glad I'm not the only one who still stumbles when using nested table calcs. I find it much easier to do everything within the advanced window and use appropriate addressing and partitioning sections.
Anyway, got this to work with LOD the following way:
1. Create custom date of continuous weeks or use datetrunc but for LOD it has to be a separate calc.
2. To get last full week:
{MAX(IIF({FIXED [Order Date (Week numbers)]: COUNTD([Order Date])} = 7, [Order Date (Week numbers)], NULL))}
We have max that is fixed for the entire data, but only looks at the weeks that are full hence the FIXED calc nested.
The rest is easy.
3. To get last full week sales:
IF [Order Date (Week numbers)] = [Last Full Week] THEN Sales END
4. To get Second to last full week sales:
IF [Order Date (Week numbers)] = DATEADD('week', -1, [Last Full Week]) THEN [Sales] END
My very rough testing has shown however that your table cals (as expected) are much quicker in this case. In some cases my LODs take 0.11s compared to your table calcs taking 0.01s top, when context filtering by Year (both used on and extract of superstore). I'd have to do those tests properly in order to know for sure what's the difference but this seems like common sense stuff considering where the calcs happen.
Jarek
This is awesome Jarek! Thanks for sharing your solution!!
DeleteHow to calculate year over year difference.. Could you plz help me
ReplyDeleteIf you're using years and want to only use completely months, just change the time dimension to months and the number of items you're counting to 12. Otherwise there's a built in YoY quick table calculation in Tableau already.
DeleteThis video is awesome, i tried to make it again with some sample data and was able to do it well. but one query where i have got stuck...i am unable to add conditional color to the WOW% like based on conditions "HIGH", "MEDIUM", "LOW" & "NEGATIVE". i tried to create a calculated field "Benchmark" like :
ReplyDeleteIF [WoW %] <=0.0 THEN "Negative"
ELSEIF [WoW %] <=0.10 THEN "LOW"
ELSEIF [WoW %] >=0.11 AND [WoW %] <=0.50 THEN "MEDIUM"
ELSEIF [WoW %] >=0.51 THEN "HIGH"
ELSEIF [WoW %] >=2.0 THEN "ABOVE 100%"
END
but no luck.... :(
if u can please help.
Can you post a link to the workbook please?
DeleteHi All
ReplyDeleteCan any one help me with the below issue
i want to convert two dates and get the week number in which those weeks lie
Suppose i have start date (27-JUN-16 ) and END DATE (16-JUL-16) It should give me a result saying that wkxx, wkyy , wkzz.. in these weeks the date lies
Hi all!
ReplyDeleteis there a way to generate a VSLY that is flexible base on the time frame the user choose? so for example if the user select may 15 it will be MOM% may 14 but if the user select 3 month or a full year this will vary?
Thanks for your help!
Ariela
I have a viz that shows units which have reached the last phase of production. My viz shows the quantity of units and the associated $value (Inception-to-Date), so the numbers only get bigger. We take a snapshot of the data twice per week. I'd like to build a viz that shows the units that have reached final phase since the last reporting period. I have Report Date and Unit ID. Please advise......
ReplyDeleteHi Andy,
ReplyDeleteThanks for the Amazing video again. I had something to point out in the formula for Completed Week sales. I think we can even do without the function "RUNNING_SUM" and just have
if WINDOW_MAX
(MAX(DATEPART('weekday',[Order Date])))=7
then (SUM([Sales]))
END
Correct me if i am wrong.
Thanks,
Jeremiah Lobo