6 Replies Latest reply on Aug 21, 2015 12:39 AM by Anshul Gupta

# Calculate last 12 months data according to selected month

Hi All,

I am stuck in a query where I have to calculate last 12 months of data on selected month, the data is of below kind:

 Month Data Last 12 Month Sum January 9 78 February 10 45 March 5 54 April 2 24 May 1 45 June 5 78 July 9 64 August 4 75 September 12 70 October 3 68 November 5 69 December 8 73

December data is consist of January to December sum of Data column, same for any other month. Data contains various years. How this can be done in tableau.

Thanks,

Anshul

• ###### 1. Re: Calculate last 12 months data according to selected month

Hi Anshul,

Have a look at this link, in Tableau Public, where I mocked up an example for you. Your question has 2 aspects:

1) how to get data from different rows into the current row, and have this calculation moving - this is a typical use case for table calculations, and I created one for you. Table calcs are very powerful, but also have their complexities, such as concepts of adressing and partitioning. If you are not familiar with these, please have a look at some of the great posts of Jonathan Drummey here.

2) How you will be filtering, so that you do not lose data for your first months. Again this calls for use of a table calc filter, that is applied later in the Tableau pipeline. You can use this post of Bora Beran, as a reference for the order of applying different filters. In essence when you filter by a table calculation, Tableau only hides the data in the viz, and this (I believe) is exactly what you need here.

I believe looking at the TWBX will get you started. Best,

• ###### 2. Re: Calculate last 12 months data according to selected month

You just saved me, the explanation and example through workbook made me completely understand the logic, thank you soooooo much, I appreciate you help.

Though I also tried to figure the logic, I ended with RAWSQLAGG_STR, I made a SQL query with a date range where clause, it gave me the result but the date range was accepting for a particular date. So I have to consolidate whole month's calculation into one, but there was formatting problem. My query was:

RAWSQLAGG_STR (

"select count(distinct data) from tablename1 1

inner join tablename2 2 on 1.column_no=2.column_no

where 1.date between dateadd(year, -1, %1) and %1

",DATETRUNC('month',[date])

)

I liked your solution better, and it gave want I actually want.

Thanks once again Vlad.

Regards,

Anshul

• ###### 3. Re: Calculate last 12 months data according to selected month

Glad it helped, Anshul! Take care!

• ###### 4. Re: Calculate last 12 months data according to selected month

I need one more help, such as if I have two columns like 'Last 12 months Sales' and 'Last 12 months Production', and with respect to selected month and year I need to calculate percentage: Last 12 months Sales/Last 12 months Production. I am trying to calculate it through directly applying it in calculated field, but its showing 1 for all months.

Thanks,

Anshul

• ###### 5. Re: Calculate last 12 months data according to selected month

How about this? I just made the same calculation for profit, and then created a calculation for the gross profit % dividing profit from the last 12 months by sales from the last 12 months. I had to then change the formatting to percentage, as it was showing 0 all over the place. I believe your problem may be just a formatting issue.

Best,