5 Replies Latest reply on Nov 15, 2018 6:04 AM by Jim Dehner

    Problem in including missing data in Window Calc, average

    Peter van der Veen

      Hi,

       

      I have a question and earlier when looking for an answer I found that a question similar to mine had already been asked by Jone Kutty here.

       

      Problem in including missing data in Window Calc, average

       

      Sadly the provided solution is not working.

       

      Calculation of "Sum of hours for the past 16 weeks":

      WINDOW_SUM(IFNULL(SUM([Gewerkte verloonde uren]),0),-15,0)

      And for v2

      WINDOW_SUM(ZN(SUM([Gewerkte verloonde uren])),-15,0)

      print1.PNG

       

      I followed all the steps of the proposed solution given by Mr. Zakovich but as you can see in my own output for some weeks (8,18,26 &33) it is not working but for others (19, 31 & 32) it is working.

      One reason I might have for why the calculation is not working might be because the missing weeks are not in the full data. This probably is because I used multiple filters and/or that is "Hours worked" is a formula too (for example to exclude the hours someone should have worked but was sick).

      I assume Tableau when howing the missing weeks just loads all the weeknumbers in the dataset and in the view when "Show empty rows" is activated. And that is what I want but sadly the calculation does not see these rows.

       

       

      Does anyone know how do I get the Window calculation to include the missing weeks?

      weekSum of hours for the past 16 weeks
      ......
      7340,5
      8340,5
      9396,5
      .......
      16756,5
      17770,5
      18718,5
      19664
      20668

       

      NB: Reason I making this workbook is to find people who are working too much so in the end I will be place a filter on the Window Calculation so that the workbook only shows everyone who worked more than say 720 hours over a 16 week period.

      Also because the dataset include privacy sensitive info I can't provide you with a workbook with this issue.