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

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

• ###### 1. Re: Calculate weekly averages per product in table whereby including days with NULL values

Hi Philip,

For every week you want to show the weekly average right

like below

Material     Week1          week2

2850          10/4=2.5          (40+11+20)/5=14.2

Like this you want to show your view or some others

Could you please provide in excel how you want to view the weekly average based on that will write a calculations accordingly

BR,

NB

• ###### 2. Re: Calculate weekly averages per product in table whereby including days with NULL values

HI Philip,

You can try looking into the below link :

• ###### 3. Re: Calculate weekly averages per product in table whereby including days with NULL values

Hello Naveen,

Thanks for the quick response. Indeed I see there is more clarification on the quesiton needed.

Thanks for the link. I will check it now.

Here is what I understood in the mean time.

I need to add an additional datasource giving a daly calender. As this is not provided in the original data. Where there is only a date when there is also a sale.

This then leads to the following view which differs from the first I sent:

Here you see that

week 1 has 5 days and

week 2 has 7 days.

So the average should be

for week 1: 10.000/5 =2000

for week 2: 71.000/7 = 10.142,9

At the end I would like on top have a table that provides me this, i.e. the weekly averages

• ###### 4. Re: Calculate weekly averages per product in table whereby including days with NULL values

Hello,

I tried AVG(ZN([Fieldnm004]))

however this doesn't do anything as this seems to effect the data level and not on the table. As Pointed out on the data level there is only a date where there is a sales. so there is no NULL to replace. The NULL is only displayed when adding a calender file with all days of the year so that also days with no sales are displayed in the table.

So I tried

WINDOW_AVG(ZN([Fieldnm004]))

however here i get the message that "ALL FIELDS HAVE TO BE AGGREGATES OR CONSTANTS"

I guess that's opening another problem,,,

• ###### 5. Re: Calculate weekly averages per product in table whereby including days with NULL values

Hi Philipp,

The calculation below gives you the weekly average by product accounting for the null days:

You would have to extract your data source for the LOD calculation to work.

I hope this solves your issue.

Emmanuel.

1 of 1 people found this helpful
• ###### 6. Re: Calculate weekly averages per product in table whereby including days with NULL values

Hello Emmanuel,

thanks for this.

Can you explain what do you mean with "you would have to extract your data source" ?

Does this mean I export the data into MS Access (here it gives me only MS access as an option)  and then reconnet to this DB file  to do the LOD calculation?

Cheers,

Philipp

• ###### 7. Re: Calculate weekly averages per product in table whereby including days with NULL values

Why I said that is because you cannot use LOD calculation on a live connection to Microsoft Access database (which your connection is).

To use LOD with your database, you'll have to extract the data. To do that, click on "Data Source" on the bottom-left and select extract as shown below:

Emmanuel.

1 of 1 people found this helpful
• ###### 8. Re: Calculate weekly averages per product in table whereby including days with NULL values

Hello Emmanuel,

THANKS A LOT !

Amazing how quick this could be solved.

smal remark I changed the formula to

{FIXED [Material],DATEPART('week', [Lieferdatum]):SUM([Fieldnm004])}/5

Cheers to you and the team!