-
1. Re: Table calculation inside a predetermined table?
Joe OppeltNov 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 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 OppeltNov 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.
-
Example_v10.4.twbx 102.0 KB
-
-
4. Re: Table calculation inside a predetermined table?
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)