Question Re: PA: Issues with DATE in Technology Q&A

PA: Issues with DATE

Former Member — Thu, 16 May 2013 14:55:41 GMT

Hi guys,

I just tried to use the R-triple exponential smoothing algorithm to create a forecast. I´m using a dataset of 2007 and 2008 and I want to forecast quarter 1,2,3 and 4 of 2009. Unfortunately PA (SP10) has some issues taking the right year. Instead PA is counting the years like shown in the jpeg attached.

Any thoughts? Regards, Christoph.

Re: PA: Issues with DATE

AndreasForster — Thu, 16 May 2013 17:14:13 GMT

Hello Christoph,

This model is expecting one single row per quarter. So you need to aggregate the records beforehand. You need something like this

Quarter,Quantity

2007-Q1, 10

2007-Q2, 12

2007-Q3, 11

2007-Q4, 13

2008-Q1, 12

2008-Q2, 14

2008-Q3, 13

2008-Q4, 15

It is also important that the data is sorted in this order.

Greetings

Andreas

Re: PA: Issues with DATE

Former Member — Fri, 17 May 2013 09:51:57 GMT

Is it possible to create a new column in PA based on two existing columns?

Re: PA: Issues with DATE

clarissa_dold — Fri, 17 May 2013 09:57:32 GMT

Hi Christoph,

yes that's possible via the manipulation tools pane or the function builder - depending on what exactly you'd like to do. There are also tutorials available which you'll find on the following page in the :

http://scn.sap.com/docs/DOC-32651

Regards,

Clarissa

Re: PA: Issues with DATE

Former Member — Fri, 17 May 2013 11:10:49 GMT

Well, if I create a new clomun like mentioned by Andreas, PA is still using the same time hierarchy as before. So how can I tell the algorithm which time hierarchie he should use? Actually as a user I would expect that PA should be able to use a DATE field or TIMESTAMP correctly

Re: PA: Issues with DATE

AndreasForster — Fri, 17 May 2013 11:46:48 GMT

Hi Christoph, it sounds like you didn't aggregate the data by quarter?

When using "R-Triple Exponential Smoothing" the input data must be aggregated by the period type you want to predict. So data that goes into the algorithm must contain only one record per quarter.

However, maybe the "Triple Exponential Smoothing" algorithm fits better for your use. There is no "R-" at the beginning of the name. This means this algorithm is not using the open-source R but the algorithm was implemented by SAP. The configuration of the two is slightly different.

With "Triple Exponential Smoothing" you get the option "Consider date column", which sounds like what you are after. Here it uses the time hierachy of a date field to aggregate the data for you. So you can have multiple records per period. The algorithm then aggregates the data per period (ie quarter) and make the prediction for the next periods.

Re: PA: Issues with DATE

Former Member — Fri, 17 May 2013 12:36:18 GMT

Hi Andreas,

thank you, its working with the algorithm you´ve mentioned. Still its not working with the R algorithm.

What do you mean by aggregate? That I have to create a new column containing YEAR and QUARTER AS QUARTER like your first column?

If I do so, how can I assign the correct column to the tool? How does PA know which column it should use for the prediction?

Re: PA: Issues with DATE

AndreasForster — Fri, 17 May 2013 15:07:09 GMT

Glad it's working now with the internal algorithm 🙂

For using the R algorithm the data needs to be summarised by quarter. That's what I meant with aggregations. So all values from Januar, February, March have to be summarised into one single row. Just like like:

Quarter,Quantity
2007-Q1, 10.0
2007-Q2, 12.0
2007-Q3, 11.0
2007-Q4, 13.0
2008-Q1, 12.0
2008-Q2, 14.0
2008-Q3, 13.0
2008-Q4, 15.0

You can copy/paste this data into a .csv and try it out.

On the prepare tab you need to convert the Quantity field to a number so that P can pick it up.
Then configure the "R-Triple Exponential Smoothing" as follows

This works as the data has to come sorted with the most recent quarter at the bottom (2007-Q1 is the first record, 2008-Q4 is the last).

Then in the configuration the Start Year (2007) and Start Period (1) tell R that the first record is the first period of 2007. Since period is set to Quarter the algorithm knows that the first row is the first quarter of 2007.

Coming back to summarising the data into quarters. This can be delegated to the database, ie
- with Universes connecting to databases
- SQL Statements
- HANA Views