8 Replies Latest reply on Sep 3, 2018 8:16 AM by Philipp Coenen

    Calculate weekly averages per product in table whereby including days with NULL values

    Philipp Coenen

      Hello,

      I have been loosing my nerves on the following problem for which i seek your help.

      As this is my first post on Tableau forum I grealty appreciate comments etc. in case I can improve the effciency on to state my request.

      Answers pls in english or in german.

       

      What: simply put I try to calculate the weekly average of sold products ( here Material) .

      Here the screenshot (Whereby Lieferdatum translates as  Delivery date)

      The Problem: Products are not sold every day. So if I just take the average it will average only on the days the product was sold but not over all days of the week.

      Example: the the product was sold on three out of 5 working days each day 10 pices then the average I'm getting is 10 = 30/ 3 days. However I need to get 30 / 5 days.

       

      My tested approaches:

      I tried a caculated field with IFNULL ( [fieldnm004], 0) to create 0 Values but this doesnt help since it would mean to replace only on the data level empty values.

      I thought about Window_AVG but couldn't get it to work.

       

      I attached the workbook extract.

      I'm working on Desktop 2018 1.4

       

      Would greatly appreciate some thoughts an comments.

       

      Best regards,

       

      Philipp