Data Viz Done Right

December 15, 2015

Tableau Tip Tuesday: How to Calculate Week over Week Change of the Last Complete Week

12 comments
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!

12 comments :

  1. I've done this using at set on WEEK(date) where the count of DAY(date) is seven.

    ReplyDelete
    Replies
    1. Ah yes! Excellent idea! I always forget about using sets this way.

      Delete
  2. Can you please give us a tutorial about set someday?

    ReplyDelete
  3. Hi Andy,

    I'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

    ReplyDelete
    Replies
    1. This is awesome Jarek! Thanks for sharing your solution!!

      Delete
  4. How to calculate year over year difference.. Could you plz help me

    ReplyDelete
    Replies
    1. If 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.

      Delete
  5. This 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 :

    IF [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.

    ReplyDelete
    Replies
    1. Can you post a link to the workbook please?

      Delete
  6. Hi All

    Can 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

    ReplyDelete
  7. Hi all!
    is 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

    ReplyDelete
  8. 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......

    ReplyDelete