2 Replies Latest reply on Nov 12, 2018 10:01 PM by Richard Michaels

    Two date fields - one parameter-10.4

    Richard Michaels

      All,

      After attending TC18, I have been tasked to transition ALL my previous Excel work into Tableau dashboards - I'm very excited, but a very overwhelmed!

      I have attached a sample of my Excel work for your reference. I believe I have two issues I need to resolve:

       

      1) On the attached Excel file, I'm trying to calculate the Annual Recurring Revenue (ARR) for each customer (column L). I have the formula worked out in Excel; however, I'm having an issue with this in Tableau.

      Below I explain how I came up with the formulas in Excel; however, should I just import my excel sheet to Tableau with the formulas already calculated and made available in a measure??

       

      The formula is Master Start Date (column BH) - Master End date (BI) this = Contract duration (column (BJ)....this is simple. I then take the Recurring ACV - Annual (column BMW) and divide this by the Contract duration to get the Daily subscription fee (column BK)....again very easy even I can do this in Tableau. Here is the hard part - I then take the Daily subscription fee and spread the revenue across each month. Since my Master start date (column BH) = Feb 23, 2018, I can recognize 6 days of revenue in Feb 2018 (Feb23rd - Feb 28). So I then take 6* my daily subscription fee and allocate $65.96 for Feb. Since there are 31 days in March 2018, I can recognize $340.77 in March (31*Daily subscription fee),......this continues until I reach my Master End Date (column BI). I have been able to create this (as you can see in Excel; however, I'm really struggling to replicate in 10.4.

       

      2) My next problem is I need to be able to aggregate the total ARR for each month (e.g. Feb 18 has =$181.31 of revenue). THis again is simple; however, in the same dashboard, I need to take my Cal.year/month and find the sum of EURO Conversion (Column AQ). I want to have ONE parameter that handles both date fields in Tableau. Is that possible?

       

      If you made it this far, CHEERS!

      All help and suggestions are appreciated