4 Replies Latest reply on Nov 20, 2018 12:53 AM by Nimrod Rosen

    Table calculation inside a predetermined table?

    Nimrod Rosen
    Nimrod Rosen Nov 19, 2018 8:33 AM

      Hi,

      I'm trying to create a chart in which I show the following result:

      new / beginning = new %

      upsell / beginning = upsell %

      downsell / beginning = downsell %

      churn / beginning = chrun %

       

      a few notes:

      1. the beginning value is a balance by nature

      2. new, upsell, churn and downsell are monthly movements.

      So the data is built in such a way that each month, the movement is added to the balance (beginning)

      3. I want to be able to change the time frame in such a way that the beginning value will remain as the value of the relevant month but all of the movement parameters will be aggregated

      So for example, if I want to look at Q3 the balance will be equal to the beginning value of the first month in that period (i.e July) and the movement will be the sum of July-September

      I have a feeling this will not be an easy feat as the "relevant month" changes in each period. but the logic should be to take the beginning value on a monthly basis and to take the first month in that period of time

      4. Last, I want to be able to filter the results based on the MRR cycle legend without the beginning option showing to the users. eventually I would want to show two options: 1) new and upsell 2)downsell and churn. I think this could be done using the case formula but if you have other ideas i'd be happy to hear

       

      attached is an example of the data

       

      Would greatly appreciate your help in this dear community!!

      P.S: of course the data is fabricated

       

      • 65 Views
      • Categories: Actions & Filters, Calculations, Dates & Times
      • Tags:
        • 1. Re: Table calculation inside a predetermined table?
          Joe Oppelt
          Joe Oppelt Ambassador Nov 19, 2018 10:31 AM (in response to Nimrod Rosen)

          (V 10.4 here)

           

          Nimrod -- I deleted the old thread.  We'll work here.

           

          For the record, to attach something new to an existing thread, click the Pulldown for "Actions" and select "Edit".  In that edit window in the lower right you'll see a link to add Attachments.  That's where you add it.

           

          So this is your raw data:

           

           

          Are you saying that for 2017-Q1, the sum of jan-Feb-Mar divided by January values?  (For each Mrr Cycle?)  And all other quarters the same treatment?

          • 2. Re: Table calculation inside a predetermined table?
            Nimrod Rosen
            Nimrod Rosen Nov 19, 2018 11:16 AM (in response to Joe Oppelt)

            Thanks for the tip

            The calculation should always be devided by the "beginning" value in the

            first month of the period

            So for Q1 17 it's devided by January values

            For Q2 17 it's devided by April values

            For Q3 17 it's devided by July etc...

            For full year 17 it's devided by January again as it is the first month of

            the period...

            But even getting a quarterly and monthly view of the data would be good.

            What's also important to me is the ability to toggle between the different

            time frames...

            I'd really appreciate any ideas on how to achieve that

             

             

            בתאריך יום ב׳, 19 בנוב׳ 2018, 20:31, מאת Joe Oppelt <

            tableaucommunity@tableau.com>:

             

            Tableau Community Forums

            <https://community.tableau.com/?et=watches.email.thread>

            Table calculation inside a predetermined table?

             

            reply from Joe Oppelt

            <https://community.tableau.com/people/joe.oppelt?et=watches.email.thread>

            in Forums - View the full discussion

            <https://community.tableau.com/message/846640?et=watches.email.thread#846640>

             

            • 3. Re: Table calculation inside a predetermined table?
              Joe Oppelt
              Joe Oppelt Ambassador Nov 19, 2018 12:00 PM (in response to Nimrod Rosen)

              In the attached, see Sheet 6.

               

              I created a parameter called Analysis Period.  Take a look at that.  It lets the user pick Year/Quarter/Month.

               

              Notice that the values of the parameter are in lower case, but the displayed values have the first character capitalized.  I did this to show you that you can display to the user the values you want them to see, but for what I need the internal values for, they have to be lowercase.


              So your user can select Year or Quarter.  (I guess I really don't need month, but there it is anyway.)

               

              Now look at [Start Date for this period].  The DATETRUNC function takes a date, and "truncates" it to the first day of the specified period.  So truncating to Quarter changes the input date to the first day of its quarter.  And you can see the results displayed on Sheet 6 for that.  DATETRUNC requires a lowercase string as the truncation part.  That's why I made the parameter's internal values lowercase.


              So this will work whether you have just first-of-the-month dates or date values all over the calendar.

               

              Now look at [Value for first month of period].  This chops the input date to the first of the month (that part is superfluous if you're only going to have first-of-the-month dates in your data.)  And it compares that to the [Start Date for this period].  For all rows in that first month of the selected period, add up the [value] amounts.  Do this at the level of [Mrr Cycle] and [Start Date for this period].  For each combination of those two measures, get the sum of [Value] for the first month of that period.

               

              And you can see the results in Sheet 6.

               

              So now you can know what the first month was, and you can use that for all the math you are looking to do.

              • 4. Re: Table calculation inside a predetermined table?
                Nimrod Rosen
                Nimrod Rosen Nov 20, 2018 12:53 AM (in response to Joe Oppelt)

                Thanks Joe, some of the things in your solution are really helpful but it's not exactly what I was aiming for:

                What I want is to sum the value of "new" for Jan-Mar, let's call it X

                then divide X by the January value in the field called "beginning" under the Mrr Cycle

                For example: for Q1-17 X is (1513+1289+1548) = 4350

                the value in the field "beginning" for Jan-17 is 14,325

                So the result would be 4350/14325 = 3%

                The value of the first month of the period should only be taken from the "beginning" field

                As I have it now, if I divide the values by the "value of the first month" the result for "New" would be 4350/1513 which is wrong

                This is what I meant by mentioning that the fields: new, upsell, downsell and churn are monthly movements

                Whereas the "beginning" field is a balance.

                so the balance in February is 14,127 (i.e beginning) , which should be equal to the starting point of 14,325 (beginning) plus all of the monthly movements.

                (in this case it's not equal because I messed with the numbers but that's the logic behind it)