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


      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




      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.

      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:



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


      1 ELSE 0



      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.