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

# Problem in including missing data in Window Calc, average

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.

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)

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.

• ###### 1. Re: Problem in including missing data in Window Calc, average

Good morning

If "Week" is part of an actual date you can use the following - note since I don;t have your workbook in a twbx format I used superstore data based on days to simulate gaps in the data

the forumla is

It will complete the series

before

after

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Problem in including missing data in Window Calc, average

Thanks for taking your time and helping me Jim really appreciate it.

But your assumption the week is part of a date is incorrect. It is actually a separate field as Tableau is giving the samenumber as we use.

Secondly If I were to replace weeknumbers with a field based on an actual date and then click on "Show Missing Values" it still won't me any data.

• ###### 3. Re: Problem in including missing data in Window Calc, average

if that is the case then you will need to pad the data to make a continuous field

see the link Vizible Difference: Data Scaffolding in Tableau

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 4. Re: Problem in including missing data in Window Calc, average

Fortunatly I also have the original date field on which my discrete weeknumbers are based in same dataset.

And it works for the date field! Thanks!

Only thing is that I have datefield with incorrect weeknumbers. I believe it had to do with Tableau using an US system and we ISO but not sure.

• ###### 5. Re: Problem in including missing data in Window Calc, average

Yes using the actual dates is much easier than scaffolding and you are also correct with the way Tableau determines weeks -

in Tableau if Jan 1 is mid week then Week 1 for the year includes the last few days of Dec and the first few of Jan

the first full week of January is then week 2

Jim