15 Replies Latest reply: Oct 30, 2014 4:44 PM by Jonathan Poole

"Trendline" in Pivot

Hello,

I have Pivot table as shown in attachment. It has two expressions =sum(TOT) (total sales so far) and =sum(BUD) (sales budget). Dimension on the top is months.

1) Now I would like to have TOT sums for the rest of the months which are not ready on the basis of previous months. In the expression what I'm looking after is that I would count Full months sales so far divide by the amount of monts and the I would have sales per month which I could use in the total sales. This is accurate enough. In the chart I would also like to these numbers which are counted based on estimation to have another color so they would be noticed. One illustration is shown below.

123456789101112Tot
2323232322,442,442,4429,33

2) And another newbie question is that I have total sums now on the right side Total TOT and BUD, how can I have another column on the end to have calculation  TOT minus BUD?

• Re: "Trendline" in Pivot

You can use an IF() statement in your expression formula to conditionally calculate the sum(TOT) or the average of preceding months.  It can be completely dynamic with the number of months.

It would be best if you can post some sample data (CSV , XLS etc...) that we can use to show possible techniques.

• Re: Re: "Trendline" in Pivot

Okay, here is one very simple example data.

• Re: Re: Re: "Trendline" in Pivot

Great.

Here is a sample.  It will work for this data set.

i calculated a month field in the load (you see it below in the column headers) and then used an IF() statement to figure out if the month is >= to the month of Today. The month of today I highlighted in red. In green are the forecast Totals based on the average of the preceding months.

Couple of things ... will your data have multiple years ?  will the current month always be today's month ? Last month ? or does it change ?

• Re: Re: Re: "Trendline" in Pivot

Thanks for this so far. Almost there.

But Yes we have multiple years. So if 2013 is chosen then we don't have any forecast on rest of the year. Forecast is always based on today's month.

And forecast is calculated in this example when we are on October based on the full months Jan-Sep and not counting Oct figures so far. So for item 123 we have sales of 35 and forecast would be then 35/9=3,9 for Oct-Dec of 2014.

And idea for the Diff would be that I would have that Diff amount only at the end. Not monthly.

• Re: "Trendline" in Pivot

Leaving the multiyear aside for a second...

I added a 'TOT Partial' field in the data model to take into account the current months values. I use that to subtract current months total so as not to interfere with the average.

What if its January, how do you forecast February -> December as there is no average to go off ?

• Re: "Trendline" in Pivot

Forecast is not done in January. When we are living in January there is no point to make forecasts for the rest of the year when we have so short time period behind. So I assume that if we would look this chart at January it would show 0 for the rest of the year and that's just fine.

• Re: "Trendline" in Pivot

Ok. I think the solution should give the right monthly numbers but ... for the Totals, would you include the forecasted numbers or just the YTD totals ?

Also the question about the DIFF showing only on the Totals ... it is possible but not straightforward. There is a klugy relatively simple method and a more involved data model based solution. But lets go through the question above first..

• Re: "Trendline" in Pivot

First of all, thanks for your input on this issue.

Totals in the end are fine as it is now, meaning that showing estimates in the sum total is okay.

And just for clarification that the DIFF in the end is okay now. Only want to get rid of the monthly DIFF column.

• Re: "Trendline" in Pivot

I fixed up the TOTALS to include the forecasted numbers.

I also went with the klugy yet relatively easy solution of adding the DIFF in the BUD cell. This eliminates the problem of not being able to suppress monthly columns while showing an aggregate for that same column.

See what you think. As i alluded too before , you can get DIFF to appear in the aggregate columns as a unique column but its more technical (out of box solution) that involves changing up the data model to introduce a new table that will include a  new month field that includes 'TOTAL' as a month (no joke) and associate TOTAL to all months in the year.

• Re: "Trendline" in Pivot

Looks okay, that DIFF solution at the end is just fine.

• Re: "Trendline" in Pivot

And I'm new to this forum also. But have you posted that latest and right solution (28th day) example file with the DIFF only at the end and multiple years..?

• Re: "Trendline" in Pivot

The XLS data set that was provided did not have multiple years.

Do you want to update / repost it with a sample that matches your situation. Then i can check it against what was done to see if it causes problems ?

• Re: Re: "Trendline" in Pivot

For sure. I already tested and of course it summed up the amount and when 2013 was chosen it tried to calculate the forecast for that year also.