10 Replies Latest reply on Dec 18, 2017 5:28 AM by Norbert Maijoor

# Displaying Average on aggregated level with dynamic date period

Hi!

I'm having forecasts for a number of articles on day level, and planned stock level for each of those articles for each day in the period.

In my graphs i visualize the planned stock as bars, and the forecast as a line. I have enabled a filter on articles, making me able to see all articles, or specific ones.

In my column I have date which is adjustable (day, week, month, year) as seen below. The planned stock is based on average per period, which is performing the way I want to.

My problem is when I aggregate several/all articles. Since I'm only taking the average of all articles, it just takes all articles in the selection of the day, and takes the average of them.

I instead want the calculation to add all demand for every day, then take the average over the filtered period, as seen below.

 Article Demand Day A 30 1 B 50 1 C 70 1 A 40 2 B 20 2 C 30 2

Average of the three articles:

Day 1: (30+50+70)/3=  50

Day 2:  (40+20+30)/3=  30

Average for period: (50+30)/2=40

This is not the way I want the calculation to be done. I want it to be done like below:

Day 1: 30+50+70=150

Day 2: 40+20+30=90

Average for period: (150+90)/2=120

Also, the calculation should only consider articles filtered.

Is this something that is possible to do in Tableau?

• ###### 1. Re: Displaying Average on aggregated level with dynamic date period

Hi Hampus,

Just as a starter;) find my approach as reference below and stored in attached workbook version 10.3 located in the original thread.

1. M1. total per day: {fixed [Day]:sum([Demand])}

2. M2. Total: {fixed :sum([M1. total per day])}

3. M3. Count day: {fixed: countd([Day])}

4. M4. Average per Period: [M2. Total]/[M3. Count day]

5. Drag required objects to the indicated locations.

Regards,

Norbert

• ###### 2. Re: Displaying Average on aggregated level with dynamic date period

Hi Norbert!

I have tried the solution you suggested, although, I bumped into some problems:

When I filter on articles, M1 doesn’t change, it still counts with the articles that are hidden/filtered. Is it possible for that calculation to adapt depending on which articles are filtered?

In below, that means in the second example with two articles, M1 for day 1 would instead be 80, and 60 for day 2.

 Day Article Demand M1 M2 M3 M4 1 A 30 150 240 2 120 B 50 150 240 2 120 C 70 150 240 2 120 2 A 40 90 240 2 120 B 20 90 240 2 120 C 30 90 240 2 120 Day Article Demand M1 M2 M3 M4 1 A 30 150 240 2 120 B 50 150 240 2 120 2 A 40 90 240 2 120 B 20 90 240 2 120

Also, I had similar struggle with M3. When I adjust date on the row level, the M3 still counts all days from the datasource.

I want it to only count the days within the selected period

If row is set on month, then I want M3 to count all the days within that month.

If row is set on year, then I want M3 to count all the days within that year etc.

Seen as example below. Is this something that is possible to do?

 Month Article Demand M1 M2 M3 M4 1 A 30 150 240 30 B 50 150 240 30 C 70 150 240 30 2 A 40 90 240 28 B 20 90 240 28 C 30 90 240 28
• ###### 3. Re: Displaying Average on aggregated level with dynamic date period

Hi Hampus,

For your first question would be the following sufficient.

M1a. Adjusted: {exclude [Article]:sum([Demand])}

To be able to answer your second question we need to know how de field Day is defined. We even need a Date field (dd-mm-yyyy) in the data structure. Is it available?

Regards,

Norbert

• ###### 4. Re: Displaying Average on aggregated level with dynamic date period

Hi Norbert!

The modification you suggested worked for M1, thank you!

About the date, yes, I have date field on the format you are describing, a few examples below:

23/10/2017

24/10/2017

25/10/2017

26/10/2017

27/10/2017

• ###### 5. Re: Displaying Average on aggregated level with dynamic date period

Hi Hampus,

Could you share a sample "dummy" dataset?

Regards,

Norbert

• ###### 6. Re: Displaying Average on aggregated level with dynamic date period

Hi Norbert!

I'm sorry about my delayed response.

Below is a dummy demand input file for Tableau, would that work out?

Regards

Hampus

• ###### 7. Re: Displaying Average on aggregated level with dynamic date period

Hi Hampus,

Find my approach based on your dummydata-set

1. M1 Demand per Day: {fixed [DET_DATE]:sum([Demand])}

2. M2. Total: {exclude [CART],[DET_DATE]:sum([Demand])}

3. M3. Count Days: {exclude [CART],[DET_DATE]: countd([DET_DATE])}

4. M4. Average per Period: {exclude [CART],[DET_DATE]: sum([M2. Total]/[M3. Count Days])}

5. Drag required objects to the indicated locations

Regards,

Norbert

• ###### 8. Re: Displaying Average on aggregated level with dynamic date period

Hi Norbert!

Above solution will work for me. Thank you for taking the time to help me out!

Regards

Hampus

• ###### 9. Re: Displaying Average on aggregated level with dynamic date period

Hi Hampus,

You are welcome. Glad it worked out for you. If above described solution is sufficient may I ask you to change the status of the thread to Helpfull or Correct Answer so the thread will be closed and the community can focus on other open threads. Your cooperation is appreciated.

Regards,

Norbert

• ###### 10. Re: Displaying Average on aggregated level with dynamic date period

Thanks Hampus!:) Appreciated!

Regards,

Norbert