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

# Table calculation inside a predetermined table?

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

• ###### 1. Re: Table calculation inside a predetermined table?

(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?

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 <

Tableau Community Forums

Table calculation inside a predetermined table?

reply from Joe Oppelt

in Forums - View the full discussion

• ###### 3. Re: Table calculation inside a predetermined table?

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?

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)