11 Replies Latest reply on Aug 19, 2013 7:08 PM by yingyingchen0

# How to show month to date % change base on the max. date selected?

I am trying to show the month to date % change in my Tableau workbook. the date for comparison would be decided based on the filter.

Say if the date range is 7/1-8/6, then MTD% would be sum(8/1-8/6)/sum(7/1-7/6) - 1; if date range is set as 7/1 - 8/5, then MTD% would be sum(8/1-8/5)/sum(7/1-7/5) - 1 = 25%

I have tried to do this if I am using now() or today() and it works, however, since I don't know the difference days between today and the max date in the worksheet, it just didn't work for me. Can anyone help?

This workbook will be uploaded to the server and new data will be added day by day. So i can't set the end date using parameter.

• ###### 1. Re: How to show month to date % change?

Say this is the sample data:

 Month of date Day of date KPI July 1-Jul-13 2,490 July 2-Jul-13 2,274 July 3-Jul-13 2,170 July 4-Jul-13 2,047 July 5-Jul-13 2,532 July 6-Jul-13 2,559 July 7-Jul-13 2,627 July 8-Jul-13 2,812 July 9-Jul-13 2,852 July 10-Jul-13 2,695 July 11-Jul-13 2,520 July 12-Jul-13 2,422 July 13-Jul-13 2,370 July 14-Jul-13 2,305 July 15-Jul-13 2,436 July 16-Jul-13 2,385 July 17-Jul-13 2,356 July 18-Jul-13 2,348 July 19-Jul-13 2,268 July 20-Jul-13 2,193 July 21-Jul-13 2,161 July 22-Jul-13 2,433 July 23-Jul-13 2,661 July 24-Jul-13 2,339 July 25-Jul-13 2,559 July 26-Jul-13 2,472 July 27-Jul-13 2,239 July 28-Jul-13 2,084 July 29-Jul-13 2,070 July 30-Jul-13 2,064 July 31-Jul-13 1,728 August 1-Aug-13 2,941 August 2-Aug-13 2,747 August 3-Aug-13 2,691 August 4-Aug-13 2,777 August 5-Aug-13 3,258 August 6-Aug-13 2,561
• ###### 2. Re: Re: How to show month to date % change?

Here is an approach. This should serve as a base for what your are trying to do. There may be other solutions depending on your desired view.

I used a parameter to define the end date and calculated fields to select the data points for the current and previous months:

[In Current Month] = [Date] >= datetrunc('month',[End Date]) and [Date]<= [End Date]

[In Previous Month] = [Date] >= datetrunc('month',dateadd('month',-1,[End Date]))  and [Date]<= dateadd('month',-1,[End Date])

[Sum of KPI (current month)] = SUM(IF [In Current Month] THEN [KPI] END)

[Sum of KPI (previous month)] = SUM(IF [In Previous Month] THEN [KPI] END)

[% MoM Change] = [Sum of KPI (current month)]/[Sum of KPI (previous month)]-1

See attached.

Pedro

• ###### 3. Re: Re: How to show month to date % change?

Hi Pedro,

Somehow I could not open up your tableau file, can you save it as Tableau 7? look at the image, it seems you are using parameter to set the end date.

The problem is, we would like a filter (date range slider) to decide the end date. since our date range goes back 13 months, and only the current month is MTD %, others are MoM%.

So i am stuck at how to do the MTD% if I am not setting up a parameter.

• ###### 4. Re: How to show month to date % change base on the max. date selected?

Sorry, I don't have v7 installed. If you post your workbook showing what you'd like to do, we may be able to offer a different solution.

Pedro

• ###### 5. Re: How to show month to date % change base on the max. date selected?

Hi Pedro,

I am not able to attach any file on this thread, it always said i was logged out. is there an email I can send it to?

since this report will be updated on the server each day, the parameter can't auto add in the new date.

And it is like what i described, can you do this without using parameter?

• ###### 6. Re: How to show month to date % change base on the max. date selected?

Why don't you publish the workbook to Public then we could download it from there. I still have v7 loaded so I post back a workbook you can open.

--Shawn

• ###### 7. Re: How to show month to date % change base on the max. date selected?

--Shawn

• ###### 8. Re: How to show month to date % change base on the max. date selected?

Sure. wallworkshawn at gmail . com

• ###### 9. Re: Re: How to show month to date % change base on the max. date selected?

Forums didn't like having a % symbol in the file name.

--Shawn

• ###### 10. Re: How to show month to date % change base on the max. date selected?

Thanks a lot, Shawn.

The sample workbook has been uploaded, can anyone take a look and see how to do the MTD%?

• ###### 11. Re: How to show month to date % change base on the max. date selected?

Hello~~~

Still looking for help, anyone?