-
1. Re: Calculate weekly averages per product in table whereby including days with NULL values
Naveen BSep 3, 2018 4:04 AM (in response to Philipp Coenen)
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
meenu choudhary Sep 3, 2018 4:47 AM (in response to Philipp Coenen)HI Philip,
You can try looking into the below link :
Null or Missing Data Produces Incorrect Average | Tableau Software
-
3. Re: Calculate weekly averages per product in table whereby including days with NULL values
Philipp Coenen Sep 3, 2018 6:01 AM (in response to Naveen B)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
Philipp Coenen Sep 3, 2018 6:16 AM (in response to meenu choudhary)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
Emmanuel Ikehi Sep 3, 2018 6:57 AM (in response to Philipp Coenen)1 of 1 people found this helpfulHi 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.
-
6. Re: Calculate weekly averages per product in table whereby including days with NULL values
Philipp Coenen Sep 3, 2018 7:13 AM (in response to Emmanuel Ikehi)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
Emmanuel Ikehi Sep 3, 2018 8:22 AM (in response to Philipp Coenen)1 of 1 people found this helpfulWhy 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.
-
8. Re: Calculate weekly averages per product in table whereby including days with NULL values
Philipp Coenen Sep 3, 2018 8:16 AM (in response to Emmanuel Ikehi)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!