1 2 Previous Next 19 Replies Latest reply on Nov 27, 2017 1:54 AM by Sanjiv Jivan

# MTD and YTD calculated field for each row

Hi,

I have data that has a QuoteDate column, a  TradeID column and a Profit & Loss "Pnl" column.  The table holds historical data for several years. What I'd like is a MTD and YTD calculated field that displays the Month to Date and Year to Date value relative to the records QuoteDate value. So if I have a report for QuoteDates from Feb 1st 2017 to March 15th 2017, the same Trade ID will be listed for each of the QuoteDates and I'd like to have MTD and YTD for each TradeID but relative to the QuoteDate. Most of the MTD and YTD Tableau examples I've come across that have a MTD and YTD calculation uses a parameter for the MTD or YTD to be computed against, or uses current day but in this case I need MTD and YTD across a date range.

Is this possible to accomplish?

Thanks,

Sanjiv

• ###### 1. Re: MTD and YTD calculated field for each row

Hi, Sanjiv

I took superstore data as example, you can use running total to calculate the YTD as shown below

Hope this could help

ZZ

• ###### 2. Re: MTD and YTD calculated field for each row

Hey Sanjiv,

I think the solution you're looking for can be found using LOD calculations. My understanding here is that you will have a column that has the various QuoteDate date values, and you will have unique combinations of QuoteDate and Trade ID for which you would like to know the MTD and YTD Profit & Loss values.

My approach would be the following, assuming that QuoteDate is already formatted as date datatype.

Calculated Field - MTD Profit & Loss

{FIXED DATETRUNC('month', [QuoteDate]), [Trade ID]: SUM([Profit & Loss])}

Calculated Field - YTD Profit & Loss

{FIXED DATETRUNC('year', [QuoteDate]), [Trade ID]: SUM([Profit & Loss]}

You can now use these fields in a view with Trade ID and various Date options as desired.

Hope that helps,

Elliott Stam - InterWorks

• ###### 3. Re: MTD and YTD calculated field for each row

Thanks for the replies. Both solutions don't quite get me what I'm looking for. For illustration consider the data

11/1/17, 100, 2000

11/2/17, 100, -500

11/3/17, 100, -400

Assume YTD as of 10/31 was 5000

11/1/17, 100, 2000, 2000, 7000

11/2/17, 100, -500, 1500, 6500

11/3/17, 100, -400, 1100, 6100

With the suggested LOD formulas, the MTD for all three rows would be 1100 which is not what I want.

With the Running Total Approach, the MTD and YTD columns would have the right value only if the report dataset included all rows for the year. In my case the report has a date range filter and I could have a date range filter. For example I could have the date fitler for only 11/2/17 and I  expect

11/2/17, 100, -500, 1500, 6500

or for a date  filter 11/2 - 11/3

11/2/17, 100, -500, 1500, 6500

11/3/17, 100, -400, 1100, 6100

Any suggestions?

Thanks,

Sanjiv

• ###### 4. Re: MTD and YTD calculated field for each row

Hi, Sanjiv

Please see my solution below as well as a sample workbook attached.

Hope this could help

ZZ

• ###### 5. Re: MTD and YTD calculated field for each row

Hi ZZ,

The results are correct when all dates are selected but as you can see if I only chose to report data for the 2nd of Nov, the MTD and YTD values are incorrect.  Selecting a report date of 2nd alone doesn't get me the right MTD and YTD values.

Perhaps I'm not applying the "report date" filter based on QuoteDate correctly as that's excluding the data from the report rather than excluding it from the display only. Feels like I would need a LOD calc for MTD and YTD to accomplish this but it's not clear to me how to do this.

Thanks,

Sanjiv

Was expecting same MTD and YTD as above for 2/11/17

• ###### 6. Re: MTD and YTD calculated field for each row

Hi, Sanjiv

Please find my updated solution attached.

Below are the sample results for your reference.

Hope this could help

ZZ

• ###### 7. Re: MTD and YTD calculated field for each row

Hi ZZ,

The sample spreadsheet works as expected however I will need to spend a little more time understanding what you did here and validate it works over an expanded dataset. Will post an update.

Thank you very much!

Sanjiv

• ###### 8. Re: MTD and YTD calculated field for each row

No worries, let me know if you got questions.

ZZ

• ###### 9. Re: MTD and YTD calculated field for each row

Hi ZZ,

I'm still a Tableau novice but I now understand what you have done in your previous solution. You created a filter on a calculated date field so that the filter on this field does not eliminate the data from the running total calculation.

However it's still not getting me precisely what I want for MTD and YTD. If we remove the AssumeYTD constant and use the following dataset

10/31/17, 100, 5000

11/1/17, 100, 2000

11/2/17, 100, -500

11/3/17, 100, -400

I would expect :

1) For 10/31/17 MTD to be 5000 and YTD to be 5000

2) For 11/1/17 MTD to be 2000, YTD to be 7000

3) For 11/2/17 MTD to be 1500, YTD to be 6500

4) For 11/3/17 MTD to be 1100, YTD to be 6100

So I need the running total for MTD to be for the month of the QuoteDate in question, and YTD to be the running total for the year of the QuoteDate in question.

Thanks,

Sanjiv

• ###### 10. Re: MTD and YTD calculated field for each row

Hi, Sanjiv

Do you have any test data includes all your cases listed above so that I can easily have a look?

Especially the YTD data, because of the lack of data, I just put it as hard code

ZZ

• ###### 11. Re: MTD and YTD calculated field for each row

Hi ZZ,

The data below covers all the use cases. It has data for mutiple trades (100, 200), for multiple multiple years (2016, 2017) and for multiple months.

For any given row, the MTD and YTD should provide the cumulative Pnl for the given trade and for the given month (MTD) and given year (YTD) respectively. So MTD for a row in Nov 2017 should not include Pnl from other months, and YTD for a row should not include Pnl from other years.

1/10/16, 100, 1000

1/10/16, 200, 1000

2/15/16, 100, -100

2/15/16, 200, 400

10/31/17, 100, 5000

10/31/17, 200, 1000

11/1/17, 100, 2000

11/1/17, 200, -100

11/2/17, 100, -500

11/3/17, 100, -400

11/5/17, 100, 300

(11/5/17 does not have Pnl for TradeID 200 as the trade is no longer active)

Thanks,

Sanjiv

• ###### 12. Re: MTD and YTD calculated field for each row

Hi ZZ,

Were you able to look at this? Or is accomplishing this in Tableau non-trivial or not possible?

Thanks,

Sanjiv

• ###### 13. Re: MTD and YTD calculated field for each row

Hi, Sanjiv

Your end result should be in quotedate level or break down to trade ID level since you introduced the multiple trade id as well?

ZZ

• ###### 14. Re: MTD and YTD calculated field for each row

Hi ZZ,

It should be Quote Date and TradeID level.  Expected MTD and YTDs for the dataset are

1/10/16, 100, 1000,1000, 1000

1/10/16, 200, 1000,1000, 1000

2/15/16, 100, -100, -100, 900

2/15/16, 200, 400, 400, 1400

10/31/17, 100, 5000, 5000, 5000

10/31/17, 200, 1000, 1000, 1000

11/1/17, 100, 2000, 2000, 7000

11/1/17, 200, -100, -100, 900

11/3/17, 100, -400, 1600, 6600

11/5/17, 100, 300, 1900,  6900

Thanks,

Sanjiv

1 2 Previous Next