PA: Issues with DATE

[Former Member]

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.

[AndreasForster · Accepted solution]

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

[Former Member · Accepted solution]

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

[clarissa_dold · Accepted solution]

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

[Former Member · Accepted solution]

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

[AndreasForster · Accepted solution]

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.

[Former Member · Accepted solution]

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?

[AndreasForster · Accepted solution]

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