You are not logged in.

Rolling 12 Calculations

Dear visitor, welcome to Palo Community Forum. If this is your first visit here, please read the Help. It explains in detail how this page works. To use all features of this page, you should consider registering. Please use the registration form, to register here or read more information about the registration process. If you are already registered, please login here.

sy@mik

Intermediate

  • "sy@mik" started this thread

Posts: 8

Date of registration: Feb 13th 2009

  • Send private message

1

Wednesday, April 15th 2009, 2:16pm

Rolling 12 Calculations

Hello,

I am trying to do a rolling 12 year calculation and the logic I am trying to use as follows

Rolling 12 for March, 2009 = March 2009 YTD + Total of 2008 –March 2008 YTD

Below is the formula I made on Palo rule editor:

['Mar R12'] = ['Mar YTD'] + PALO.DATA("MyDB", " MyCube", !'Countries & Centres', !'Measures', !'Scenario', "Year", VALUE(!'Years') - 1, !'Department & Product', !'Currencies') - PALO.DATA("MyDB", " MyCube", !'Countries & Centres', !'Measures', !'Scenario', "Mar YTD", VALUE(!'Years') - 1, !'Department & Product', !'Currencies')

This expression gives me error because “VALUE(!'Years') – 1” returns an integer datatype which is not acceptable by Palo.

Could anybody please help me creating a rolling 12 calculation? I have already done YTD calculations in this instance.

Thanks in advance

Sunil

h_decker

Sage

  • "h_decker" is male

Posts: 763

Date of registration: Oct 26th 2005

Location: Cologne, Germany

  • Send private message

2

Wednesday, April 15th 2009, 2:24pm

RE: Rolling 12 Calculations

Hi,
try
STR(VALUE(!'Years') - 1,4,0)

Maybe this helps.

Holger

sy@mik

Intermediate

  • "sy@mik" started this thread

Posts: 8

Date of registration: Feb 13th 2009

  • Send private message

3

Wednesday, April 15th 2009, 2:30pm

RE: Rolling 12 Calculations

Hi Holger,

Thanks for Quick reply. Could you please explain a bit about the parameters passed to STR Function.

what is that 4 and 0 used for?

Sunil

h_decker

Sage

  • "h_decker" is male

Posts: 763

Date of registration: Oct 26th 2005

Location: Cologne, Germany

  • Send private message

4

Wednesday, April 15th 2009, 2:47pm

RE: Rolling 12 Calculations

Hi,
4 is total lengh of resulting string and
0 is the number of decimal places.

Thats what Rule Editor says.

Holger

sy@mik

Intermediate

  • "sy@mik" started this thread

Posts: 8

Date of registration: Feb 13th 2009

  • Send private message

5

Wednesday, April 15th 2009, 2:58pm

Thanks Holger for your help. I will try it now

sy@mik

Intermediate

  • "sy@mik" started this thread

Posts: 8

Date of registration: Feb 13th 2009

  • Send private message

6

Wednesday, April 15th 2009, 5:13pm

Hello,

I have finally managed to do R12 calculations correctly as follows.

['Mar R12'] = ['Mar YTD'] + IF(PALO.EFIRST("MyDB", "Years") != (!'Years'), PALO.DATA("MyDB", "MyCube", !'Countries & Centres', !'Measures', !'Scenario', "Year", STR(VALUE(!'Years') - 1, 4, 0), !'Department & Product', !'Currencies') - PALO.DATA("MyDB", "MyCube", !'Countries & Centres', !'Measures', !'Scenario', "Mar YTD", STR(VALUE(!'Years') - 1, 4, 0), !'Department & Product', !'Currencies'), 0)

I would be greatful, if someone could advice a better way of calculating R12 values as I am not sure how flexible and reliable this formula will be in long run

Thanks in Advance.

This post has been edited 2 times, last edit by "sy@mik" (Apr 15th 2009, 5:35pm)

Rate this thread