6 Replies Latest reply on Sep 3, 2018 2:10 AM by Rafał Świeży

# Average sales based on running total of sales and day of the month

Dears, this is my first post, I am new user of tableau, in case I am writing in wrong place or something let me know.

I have a sales data rows like below:

DateSales Ammount
1.08.20181
1.08.20182
2.08.20182
2.08.20183
3.08.20185
3.08.20181
3.08.20181

I want to have output table like below:

DateRunning SUM
Average Daily Sales
1.08.20183=[Running Sum] / Day[Date]
2.08.20187=[Running Sum] / Day[Date]
3.08.201814=[Running Sum] / Day[Date]

As you know I cannot create this kind of calculation because "I cannot mix Aggregate and non-aggregate arguments with this function".

Generally I have data for each day since 2012 JANUARY for differnet departments, so at the and I want to comapre different years for different departments. Just to say that data is more complex then my example but i believe my example is enough for you people to help me

I want to have a 1 month view.

Waiting your help thx!

• ###### 1. Re: Average sales based on running total of sales and day of the month

is this what you were expecting?

1 of 1 people found this helpful
• ###### 2. Re: Average sales based on running total of sales and day of the month

see the attached

I had to convert the date using

and this will be the running total

and the avg

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.

1 of 1 people found this helpful
• ###### 3. Re: Average sales based on running total of sales and day of the month

This is perfect, thank you! I get numbers I wanted, I have one additional question, do you have any idea how to now show on the chart connection between days which are weekends? I have a daily month overview and when is weekend(no sales) I have empty cells, any idea how to also count those days to average? These rows in data don't exist, but I would like to count them to average also...

• ###### 4. Re: Average sales based on running total of sales and day of the month

thank you! the solution from you seems more complicated then previous idea but still works, or maybe it there is fundamental difference?

I have one additional question, do you have any idea how to now show on the chart connection between days which are weekends? I have a daily month overview and when is weekend(no sales) I have empty cells, any idea how to also count those days to average? These rows in data don't exist, but I would like to count them to average also...

• ###### 5. Re: Average sales based on running total of sales and day of the month

Hi ,

I dont think there should be any issue since its running sum of sales and you are dividing by day number.

1 of 1 people found this helpful
• ###### 6. Re: Average sales based on running total of sales and day of the month

Thank you I found a solution!