6 Replies Latest reply on Oct 8, 2014 4:49 AM by Sourabh Dasgupta

# Side-by-side bar, Daily average vs Today's total

Hello,

Is it possible to create side-by-side bar where one bar will show today's sum of transaction and another one daily average for the last year? I'd like to see the difference between my total amount of transactions over the last day and daily average amount of transaction.

Thank you

• ###### 1. Re: Side-by-side bar, Daily average vs Today's total

1. Create a calculated field like "if [Order Date] = Today() THEN [Sales] END" - TODAY SALES

2. Create a calculated field like "if Datepart('year', [Order Date]) = Datepart('year', Today()) - 1 THEN [Sales] END - LAST YEAR SALES

Now use any dimension and these two measures and create a side-by-side bar chart. For the "LAST YEAR SALES" measure select the aggregation type as AVG.

• ###### 2. Re: Side-by-side bar, Daily average vs Today's total

Hi Sourabh,

Thank you for your answer. Your solution allows to compare an average size of sale over the last year with sum of today sales.

What I'm looking for is  a solution for another problem. I want to compare:

1. Daily average, e.g. AVG(SUM(sales in Jan,1), SUM(sales in Jan,2), ..., SUM(sales in Dec, 31))

2. Sum of today's sales

Maxim

• ###### 3. Re: Side-by-side bar, Daily average vs Today's total

one update,

Indeed it can be calculate as SUM(sales over the year)/[number of working days]. Unfortunately I've no idea how to calculate a number of working days including holidays for different locations.

• ###### 4. Re: Side-by-side bar, Daily average vs Today's total

Hi, please see the attached workbook.

1. Get the required dimensions in the Column Shelf (in this case this is Product Category)

2. Create a calculated field as: Window_avg(SUM(Sales)) - this is a table calculation. After the Sum(Sales) is calculated Tableau will calculated the average of all. We want to calculate the SUM(sales) at the day level so we need to Bring the DAY field in the detail shelf, so SUM(sales) will be calculated at the day level - (the step 3 takes care of this)

3. Drag the [Order Date] in to detail shelf - right click and select "DAY" Continuous level and make it discrete. Please check the workbook for the option selected.

4. Select "Analysis" menu, and select STACK MARKS - OFF. Since we have DAY field in detail shelf, the bar for the average will create stacks for each day. This is not required and we want only one value to be shown

5. Use the "Today Sales" calculated field to now create the side by side bar. - I have added a parameter to select a Date instead of restricting to TODAY.

Let me know if this is what you were looking for.

• ###### 5. Re: Side-by-side bar, Daily average vs Today's total

Hi Sourabh,

Thank you very much! That's exactly what I was trying to create.