12-21-2015 07:04 AM - edited 12-21-2015 07:10 AM
I'm trying to derive an aggregated percent-differential value from the picture below, for each year. I've done it in Excle, but would like to do it in SAS VA as well.
What I'm calculating is the differential kiloWattHours [kWh] from 2011-2014.
I'm on the track that I have to use the Parallel period function, but I'm not sure. I Hope the picture explains itself.
12-23-2015 04:47 AM - edited 12-23-2015 04:49 AM
Instead of 'FULL', You can take 'Today', it depends on your requirement.
Hope this helps you..
Let me know...,
Thanks & Regards,
01-04-2016 04:55 AM
Thanks for your suggestion. I found the derived function 'Year to Date Growth' shortly after I posted my question. So now it looks like the picture below. (Be aware, it's new kWh Avg pr. Year values as the values in the thread-start were wrong, minor detail).
With the growth funtion:
But something seems to be wrong. If I manually calculate the growth:
'11-'12: [(5291-5359)/5359] = -1.2%
'12-'13: [(5035-5291)/5291] = -4.8%
'13-'14: [(4802-5035)/5035] = -4.6%
'14-'15: [(4335-4802)/4802] = -9.7%
I get these values from year to year. They somehow seem to approximate the values on the red-line graph with a little deviation, although the growth from '14-'15 is totally deviated by ~ -7%?
01-04-2016 09:10 AM
01-04-2016 11:00 AM
01-05-2016 02:15 AM - edited 01-05-2016 02:20 AM
That also might seem to be the issue. The 'kWh Avg. pr. Year' is an aggregated value. This value is a subset of (Sum _ByGroup_ (kWh) / SX Bruger-id), as you can see on the illustration below. I needed this aggregated variable as I want to calculate the average kWh usage pr. year. The SX-Bruger-ID is just a uniqe ID for each customer/user.
This prevents me from right clicking on 'kWh Avg pr. Year' and creating the growth. Any ideas?
If I assign the "kWh" measure to the graph and set the aggregation to Average, then I',m getting the daily average pr. year, as my lowest level of kWh data is pr. day, but I want it presented as an average for the year. And to make it clear, no it doesn't change any numbers on the red growth line.
01-05-2016 03:58 AM
Yes, there is not much to share else than it's a unique ID for each customer. For example LL000001, LL000002....etc.. It's just to use an ID to identify my customers.
01-05-2016 09:06 AM
When you apply kWh to the bar chart and set the aggregation to Average, you would not expect the red line to change (that comes from your aggregated kWh Year Growth item) but the bar values should be different, and I would expect that the difference in the bar values would match the values shown on the red line.
For example, the difference between the 2014 and 2015 bars should match the -2.03 value indicated on the red growth line when calculated manually.
01-05-2016 09:53 AM
@Sam_SAS, thank you for that. You are right, I could replicate the numbers as my kWh is represented for each day in the dataset. Applying the kWh to the graph resulted in the average kWh pr. day pr. year which matched the growth function of the red-line and the numbers were correct. But I'm still interested in showing the kWh average pr. year (NOT average pr. day pr year) and add the growth line for these years. I'm missing something, but can't figure out what.
01-05-2016 01:16 PM
01-06-2016 02:00 AM
@Sam_SAS, I just think this is a very basic thing. It's an regular average calculation saying expressing sum of kWh devided by sum of customers. On the other hand this gives me the experience of SAS VA not having the ability to create growth calculations on aggregated variables.
01-06-2016 04:55 AM - edited 01-06-2016 04:56 AM
@Sam_SAS, Got the result. It's working Although I had to do it in my backend.
One additional question. On my x-axis I had years in the previous graph, what if I ie. used segments, and want the growth function to represent the growth from '11-'15 based on the segments. I see that I don't have any variable representing years, see below. Any ideas?
I would like the results to appear like on the graph below. OBS! Please don't take care about the values in the graph below as they are wrong.
01-06-2016 09:36 AM
Normally when you use an aggregated item with a periodic operator, your graph would contain a date or time variable and really it should be the same date variable that is the basis for the periodic operator.
When you add your Growth aggregated measure to a barline chart where the category axis does not contain a date/time variable, it returns missing values.
If you calculate the Growth measure as part of your data preparation, that might make a difference but I'm not sure I understand why you would want to plot growth against category values rather than date/time values.