Joined: 28 Nov 2012
Location: United States
Online Status: Offline
Posts: 62
Topic: Calculate from SUM Posted: 14 Mar 2016 at 11:15am
I have a table consisting of YEAR, MONTH, DOLLAR, and 2 different CATEGORY.
2014 1 $330.40 $742.61
This would represent the Category sales for Jan of 2014... call them category A and category B. The data is sorted by year and month, then grouped by Year and a SUM of the columns are at the group's Footer.
The issue is, I also need to calculate the growth year after year so I need to calculate Jan 2015 minus the sales of Jan 2014 to figure out the difference. I can't use the footer SUM to do this or I'm just subtracting it from itself.
Any suggestions on an easy fix?
Thank you in advance!
Joined: 28 Nov 2012
Location: United States
Online Status: Offline
Posts: 62
Posted: 15 Mar 2016 at 5:49am
This is getting very close... I like it so far. I understand and got the sales for the Year as suggested. However, I need to compare the month of January 2015 to the month of January 2014.... And then the month of January 2016 to the month of January 2015.
Would I have to create a running total for each month in each year?
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Posted: 15 Mar 2016 at 8:16am
that is one possibility.
Another approach is to create one formula field to set a negative value for last year's data and a positive value for this years data and then just sum that. you could also use a CrossTab set (grouped) to the month value and sum it per month to show the difference on each month as well as the total for the year.
if table.year = year(today)-1 then table.dollars *(-1) else table.dollars
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
