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

    Sanjiv Jivan

      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
          Zhouyi Zhang

          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
            Elliott Stam

            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
              Sanjiv Jivan

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

               

              QuoteDate,TradeID,Pnl

              11/1/17, 100, 2000

              11/2/17, 100, -500

              11/3/17, 100, -400

               

              Assume YTD as of 10/31 was 5000

               

              QuoteDate,TradeID,Pnl,MTD,YTD

              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

               

              QuoteDate,TradeID,Pnl,MTD,YTD

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

               

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

               

               

              QuoteDate,TradeID,Pnl,MTD,YTD

              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
                Zhouyi Zhang

                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
                  Sanjiv Jivan

                  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

                   

                  all-dates.png

                   

                   

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

                  only-2nd.png

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

                    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
                      Sanjiv Jivan

                      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
                        Zhouyi Zhang

                        No worries, let me know if you got questions.

                         

                        ZZ

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

                          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

                           

                          QuoteDate,TradeID,Pnl

                          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
                            Zhouyi Zhang

                            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
                              Sanjiv Jivan

                              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.

                               

                              QuoteDate,TradeID,Pnl

                              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
                                Sanjiv Jivan

                                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
                                  Zhouyi Zhang

                                  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
                                    Sanjiv Jivan

                                    Hi ZZ,

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

                                     

                                    QuoteDate,TradeID,Pnl, MTD, YTD

                                    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