4 Replies Latest reply on Jan 20, 2016 2:43 PM by Shawn Wallwork

# Exclude uncomparable data (by month) at aggregated level

Is it possible to exclude data for stores that have not comparable month level data from a total aggregated data set?

In the attached file, I wish to create a bar chart, showing growth of all stores in my company, only for stores that have full data sets for both years. If Store 3 doesnt have September data for 2012, I want to exclude Store 3 entirely from my total aggregation of sales across the two years.

Basically the intention of this exercise is for me to compare stores that have FULL comparable data.

Thanks!

• ###### 1. Re: Exclude uncomparable data (by month) at aggregated level

You can create custom dates for years and months from your order date field by right clicking on the date field > create > custom date

Then use LOD expression to return sales value only if the months appear in both years fixed at a level of store name by creating a calculated field like:

IF { FIXEd [Store Name]: min( IF { fixed [Store Name], [Order Date (Months)] : countd([Order Date (Years)]) } <= 1 then 0 else 1 end) } = 1

then [Sales] else 0 end

So basically the inner IF expression tells Tableau to place a 0 for any stores where count of years is less than or equal to 1 and place a 1 otherwise. Then the outer IF tells Tableau to identify if the result of the inner IF is 1 then throw a sales value else throw a 0.

Then you can use years on columns and this calculation on rows instead using sales on rows.

See attached version 9.2 WB.

• ###### 2. Re: Exclude uncomparable data (by month) at aggregated level

Dang, I was too slow, Pooja beat me to the punch.   Oh well, I'll post my approach anyway....

My approach was a wee bit different. I was doing the even/odd thing. Create two calcs:

Month of Order Date

{ FIXED [Store Name], [Year of Order Date] :

COUNT( MONTH([Order Date]) ) }

Both Years Equal?

{ FIXED [Store Name], [Month of Order Date]: COUNT([Month of Order Date]) } % 2 =

{ FIXED [Store Name], [Month of Order Date]: COUNT([Month of Order Date]) } % 1

Note: When you create these T is going to make them Measures. Just drag them to the Dimension window.

You'll put the Both Years Equal? pill on the filter shelf and set to True and get this:

9.2 workbook attached.

Cheers,

--Shawn

1 of 1 people found this helpful
• ###### 3. Re: Exclude uncomparable data (by month) at aggregated level

Aah! Nice, I like that modulo approach.

• ###### 4. Re: Exclude uncomparable data (by month) at aggregated level

Uh-oh. This won't work if one year has 4 months and the other one has 6 months. You should probably go with Pooja's approach.

--Shawn