9 Replies Latest reply on Jul 8, 2017 9:50 AM by khalida nihal

# Calculate last 1 year data in tableau

Hello All,

I am trying to fetch the last one year data in tableau from the month in which the report is run. For Example, If I run the report on July 2017  then the report should portray "July 2016 -  July 2017" data.   I have attached sample data in the excel. I need to represent below 2 reports in graphs:

• When ledger='AA' represent past 1 year data for POH and SGA
• When ledger ='AA' represent past 1 year data for GM %age of Revenue.

GM %age of revenue is a calculated field. Please find attached the workbook as well which shows these values. Please help me  to resolve this.

Thank you

• ###### 1. Re: Calculate last 1 year data in tableau

You can create a calculated field as below and use this as filter selected TRUE.

Ak

• ###### 2. Re: Calculate last 1 year data in tableau

hi

There is no date field in my data. using just the month(jan,feb etc) or period(which is in numbers). In this case how will I be able to fetch the data?

Thanks,

Khalida

• ###### 3. Re: Calculate last 1 year data in tableau

I have just changed the source format as below. please have a look and let me know how to calculate past 1 year data and use it in an automated fashion.

• ###### 4. Re: Calculate last 1 year data in tableau

Please use the latest data format. I need to calculate the past 1 year data using this format. kndly help.

• ###### 5. Re: Calculate last 1 year data in tableau

Hi Khalida,

First of all, this format is not appropriate because the cells are merged.

Then after connecting to this excel, choose data area and change them to Number decimal

Then

Select same data area and Pivot data.

Or you need each value separately, you can create individual field to create more calculated field.

etcetc

Thanks,

Shin

• ###### 6. Re: Calculate last 1 year data in tableau

Hello Khalida,

As Shin mentioned you need to pivot your data, after this you just need to create 2 calculated fields

1) Date Month =

MAKEDATE(2000+[FY],

CASE TRIM( SPLIT( [Month], " ", 2 ) )

WHEN 'Jan' THEN 1

WHEN 'Feb' THEN 2

WHEN 'Mar' THEN 3

WHEN 'Apr' THEN 4

WHEN 'May' THEN 5

WHEN 'Jun' THEN 6

WHEN 'Jul' THEN 7

WHEN 'Aug' THEN 8

WHEN 'Sep' THEN 9

WHEN 'Oct' THEN 10

WHEN 'Nov' THEN 11

WHEN 'Dec' THEN 12 END,1)

2) DATEDIFF('month,TODAY(),[Date Month])=12.

Please try this and let me know, if you still have nay question.

Ak

• ###### 7. Re: Calculate last 1 year data in tableau

Hello Shin,

Thank you for your response. I have changed the source data accordingly in order to get the report format. Please find attached the source format,tableau workbook and the report format i am trying to achieve. Could you help me to achieve this report format for last 1 year data.

Also I am unable  to get the month names as 'Actual Apr 2016', or 'Actual Dec 2017' while displaying the report(as shown in 'Report Format' attached). is there a solution for this?

• ###### 8. Re: Calculate last 1 year data in tableau

Khalida

Here you go.

Thanks,

Shin

• ###### 9. Re: Calculate last 1 year data in tableau

Thank You so much Shin.

I am going to try this on my live data now. The date month field which I have created using makedate, does it work well for live data as well?

Also, I am trying to put a month filter on the sheet such that each time I choose a different month,the respective last year data gets displayed. For example, Mar filter month dispalys april2016 to Mar 2017 and July filter displays Jun 2016  to july 2017 data. How can I achieve this?