14 Replies Latest reply on Nov 26, 2016 12:56 PM by Yuriy Fal

    payment amount assigned to periods

    Peter Cox

      Dear all,

       

      I have a table with multiple payments from users which I would like to assign to periods. A subscription is paid in advance and should be correctly allocated to the next 3 or 12 months. I prefer to have a table as output.

       

      Below I provided an example for a payment of EUR 1188 from user 75 which should be allocated to the next twelve months (payment plan id = 3) based on the number of days per month. I want to have this for each user_id, so each user_id in a row.

      To table should look like:

       

      I couldnt find a discussion which discussed this before so I hope you guys can help me. Please find attached a workbook with the sample data.

       

      Many thanks in advance!

       

      Best,

      Peter

        • 1. Re: payment amount assigned to periods
          Shinichiro Murakami

          Your goal is pretty difficult to be achieved by Tableau with given data..

          Because this requirement is more focus on how to calculate the amount on future each month.

          Excel or something other calculation tool will meet expectation for sure.

          But Tableau, to show the amount under non-existing header like future date with calculated value is something not assumed to do.

           

          To tell the truth, there might be some workaround, but that should be SUPER complicated.

           

          In you case, you can easily calculate the amount by month in Excel, and after then rely on Tableau to show the graphical view is better approach.

          Don't ask Tableau to calculate the amount by month.

           

          If I can consider current data set is also available as excel, I might be able to provide alternatives?

           

          Thanks,

          Shin

          • 2. Re: payment amount assigned to periods
            Peter Cox

            Hi Shin,

             

            Although its not easily I do believe that there is a solution for this. There is another discussion you participated in on Tableau which looks quite similar of what I want but there you are working with pivot tables(?): How to calculate accrual and deferral for subscription based business

             

            There is also another discussion on the community which could have the same outcome but has a different layout. Re: Divide a value between months In this discussion they have defined a table with the periods. Maybe there is a solution in that direction.

             

            Please find attached the excel files with sample data.

             

            Thanks!

            Peter

            • 3. Re: payment amount assigned to periods
              Shinichiro Murakami

              Peter,

               

              You are right. As I said, if I can modify original data little bit, I think Tableau can solve it.

              At the same time, you can find I mentioned exact same thing that Tableau is not a calculation tool. 

               

              Means this is not typical practice how to use Tableau and better way is to have better organized data, anyways.

               

              I don't find "payment date" on neither of excel file.

              Could you specify the which field/how I can refer as "payment date"?

               

              Thanks

              Shin

              • 4. Re: payment amount assigned to periods
                Peter Cox

                Hi Shin,

                 

                We are using a UNIX timestamp for dates which is the column with "created" in the Excel file named "payments". I created a calculated field as follows:

                In Tableau: DATEADD('second',INT(([Created])+(1/24)),#1970-01-01#)

                In Excel: =(((COLUMN_ID_HERE/60)/60)/24)+DATE(1970,1,1)

                 

                Best,

                Peter

                • 5. Re: payment amount assigned to periods
                  Shinichiro Murakami

                  Peter,

                   

                  If I can modify excel, I will use attached.

                  Use Sheet "payments-EXL2"

                  Then pivot with Date Header.

                  Then you can create any VIZ easily.

                  This is "Payment_Excel_SM_9.2"

                   

                  Other Option is use "payments-for_Tableau", but I don't find the advantage with this in the case you can create attached excel file.

                  Just FYI I also attached Table file "Payment_Tableau_SM_9.2" which is incorporate with data of "payments-for_Tableau"

                   

                  Thanks,

                  Shin

                  • 6. Re: payment amount assigned to periods
                    Yuriy Fal

                    Hi all,

                     

                    There may be a solution of sort right in Tableau.

                    But it does require some re-shaping of the datasource --

                    namely, padding dates to have future periods to post.

                    This is done via join with the calendar table (plus a calc).

                     

                    The logic behind the Posting routine is simple --

                    the first and the last periods assumed incomplete,

                    so the total days are full N periods (3 or 12)

                    minus # days from [Date Created] till the end of the month.

                     

                    Please find the attached.

                    Hope it could help a bit.

                     

                    Yours,

                    Yuri

                    • 7. Re: payment amount assigned to periods
                      Shinichiro Murakami

                      Thank you Yuri as usual.

                       

                      This formula is pretty complicated, I will try to comprehend.

                      I may be able to understand step by step drill down/up.

                       

                      You are using Tableau like Excel.

                       

                      [Date Posted]

                      IF [Date1] = {MIN([Date1])} THEN DATETRUNC('month', [Date Created])

                      ELSE

                      IFNULL(

                      DATEADD  ('month',

                      DATEDIFF ('month',

                      DATETRUNC('month', {MIN([Date1])}),

                      DATETRUNC('month', [Date Created])),

                      DATETRUNC('month', [Date1])),

                      DATETRUNC('month', [Date Created]))

                       

                      END

                      • 8. Re: payment amount assigned to periods
                        Yuriy Fal

                        Hi Shin, I try to explain:

                         

                        [Date Posted]

                         

                        // The first row calculates the 1st Posting period (for each User ID & ID)

                         

                         

                        IF [Date1] = {MIN([Date1])} THEN DATETRUNC('month', [Date Created])

                         

                        ELSE

                        IFNULL(

                         

                        // The next 5 rows are just for the 2nd Posting period to be the next Month to [Date Created]

                        // The DATETRUNC on [Date1] is not necessary, as I'm using the 1st day of the Month for each [Date1], left for consistency.

                         

                        DATEADD  ('month',

                        DATEDIFF ('month',

                        DATETRUNC('month', {MIN([Date1])}),

                        DATETRUNC('month', [Date Created])),

                        DATETRUNC('month', [Date1])),           

                         

                        // This is the only Posting period for any payment that doesn't require Posting

                         

                        DATETRUNC('month', [Date Created]))

                         

                        END

                         

                        END :-)

                        • 9. Re: payment amount assigned to periods
                          Shinichiro Murakami

                          Thank you again Yuri.

                           

                          My surprise is that you judged that you can create this type of formula from the given concept.

                          My pocket is still small, but keep learning!!

                           

                          Thanks,

                          Shin

                          • 10. Re: payment amount assigned to periods
                            Yuriy Fal

                            Actually, I've made the same thing before (for a customer).

                            The common theme is snapping actuals and forecast (calculated elsewhere).

                            • 11. Re: payment amount assigned to periods
                              Peter Cox

                              Hi Yuri,

                               

                              Thanks for your solution. This kind of table is what I am looking for!

                               

                              If I understand correctly, I do need to add a column "Link ID" with only value "1" in the payments table so this one is related to the calendar table? Can this be done by a calculated field or does this column needs to be hardcoded in the source table? And then for each specific payment plan (we have more than the ones mentioned) I have to include the specific dates?

                               

                              It looks like it doesn't make sense if the dates are 2014 or 2016 in the calendar table?

                               

                              Best,

                              Peter

                              • 12. Re: payment amount assigned to periods
                                Yuriy Fal

                                Hi Peter,

                                 

                                You've got it right -- no need for Link ID (thanks for catching :-),

                                because the tables are joined already on Payment Plan ID.

                                 

                                Which dates to have in the calendar table -- no matter at all.

                                But for each Payment Plan ID the date range

                                should start from the same date --

                                thus making {MIN([Date1])} LOD working.

                                 

                                Yours,

                                Yuri

                                • 13. Re: payment amount assigned to periods
                                  Peter Cox

                                  Hi Yuri,

                                   

                                  Thanks. I applied the sample data on my own work sheet and its working fine! Thanks a lot for your help

                                   

                                  Kind regards,

                                   

                                  Peter

                                  • 14. Re: payment amount assigned to periods
                                    Yuriy Fal

                                    Peter, you're welcome.