9 Replies Latest reply on May 4, 2018 7:57 AM by erik.furlanis

    LOD challenge: calculate the number of weeks products have sold to display total sales chart by week

    erik.furlanis

      Hi all,

       

      I am trying to achieve a logically simple calculation, which seems though hard to be rendered in Tableau.

      I find it hard to explain it with words, so I'll just kick off with an example, sales of four products by week

       

      Product
      Week1Week2Week3
      A201030
      B10712
      C5127
      D564

       

      What I want to do essentially is to flag products which do sell more than 10 units a week and subsequently I want to filter in only products that sell more than 10 units a week in at least 2 weeks.

      Therefore, only product A and B would be filtered in.

       

      I then want to show in a chart the total sales of this sub-selection of products, i.e.

      Week1Week2Week3
      total sales30 (20+10)17 (10+7)42 (30+12)

       

      I find it hard to achieve it in Tableau because I essentially first need to count the number of weeks that a product is above 10 units and check if it happens for more than 2 weeks. To achieve this I need to essentially lose the Week dimension and use an Include LOD.

      But then I need to bring back the week and lose the product dimension, and that is when things are getting hairy; I have used nested LODs without too much luck.

       

      I achieved to filter in the products worth of consideration with the following:

       

      IF

      SUM({Include [WEEK] : (IF SUM([SALES]) > 10 THEN 1 ELSE 0 END)}) >= 2

      THEN

      1 ELSE 0

      END

       

      Does anyone have any suggestion?

       

      Also to be kept in mind, I would like to make the two limits (how many units per week per product and how many weeks the minimum sales is met) dynamic by means of a parameter, but I expect this can be swiftly implemented with parameters once I get this calculation right.

       

       

       

      Thanks!