1 Reply Latest reply on Nov 26, 2012 7:01 PM by Catherine Rivier

Is there a way to show rolling current month, last month, % Change, and year to date totals for multiple measures in a single cross tab?

Hi,

I've been looking through the forum and trying to get tableau to create the cross tab below. Ideally the table below would update as new monthly data is appended. I've been able to get tableau to calculate each of the % changes by measure but have not been able to format a single % change calculation for multiple measures for a rolling 2 months.

Any help or insight would be appreciated!

Thanks!

Measures
This Month (Nov 2012)Previous Month (Oct 2012)% Change (M/M)YTD
Impressions10050100%2,000
Clicks2010100%100
Cost151050%1,000
• 1. Re: Is there a way to show rolling current month, last month, % Change, and year to date totals for multiple measures in a single cross tab?

Hi,

This is completely do-able with a few calculated fields using the datetrunc function.  It's much easier and more efficient, I find, to use that rather than built in date calculations when you use a lot of different calculations like you have.

In the attached workbook, I created 5 new date fields, and 4 new calculations.  (Note because I'm using the Coffee sample data and the dates only go through 12/2011, I created a fake variable to use instead of today, called "TodayFAKE" - in your situation you'd use Today())\

Date extraction based on current time:

• CurrentMonth:  date(datetrunc("month",Today()))
• CurrentYear:  date(datetrunc("year",Today()))
• LastMonth: date(datetrunc("month",(datetrunc("month",Today())-1)))

Date extraction based on the data:

• DateMonth: date(DateTrunc("month",[Date]))
• DateYear: date(DateTrunc("year",[Date]))

Then create the 4 fields you have shown in your sample:

• Sales-CurrentMonth: IF [DateMonth]=[CurrentMonth] THEN [Sales] ELSE 0 END
• Sales-CurrentYear: IF [DateYear]=[CurrentYear] THEN [Sales] THEN [Sales] ELSE 0 END
• Sales-LastMonth: IF [DateMonth]=[LastMonth] THEN [Sales] THEN [Sales] ELSE 0 END
• Sales-MonthDifferencePercent: (sum([Sales-CurrentMonth])-sum([Sales-LastMonth]))/sum([Sales-LastMonth])

And do the same with any other additional calculations you want to do.

1 of 1 people found this helpful