You are not logged in.

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.

jdenegri

Professional

  • "jdenegri" started this thread

Posts: 20

Date of registration: Jan 5th 2012

  • Send private message

1

Friday, March 23rd 2012, 5:08pm

Creating a dimension with fiscal year.

Hi, I'm trying to create a dimension that includes months of two years. For example: starting on oct 2011 and ending on sept 2012.

I could create all months including their year so when I consolidate I create the corresponding fiscal year but that solution is not generic. For example oct 2011, nov 2011, dic 2011, jan 2012 and so on.

I looking for a generic solution, if anyone could help me I would really appreciate that.

Thanks

Joaquin.

kratzer

Master

  • "kratzer" is male

Posts: 92

Date of registration: Feb 23rd 2006

Location: Karlsruhe

Occupation: IT

  • Send private message

2

Friday, March 23rd 2012, 7:18pm

Using two separate dimensions for Year and Month won't be flexible? Only the order of the elements in the month dimension would change in contrast to Calendar Year.

jdenegri

Professional

  • "jdenegri" started this thread

Posts: 20

Date of registration: Jan 5th 2012

  • Send private message

3

Friday, March 23rd 2012, 8:04pm

I don't think so, because how do you consolidate after creating the two dimensions?

For example the consolidate year is 2010-2011 and the months goes from oct to sept.

Thanks for the quick reply.

kratzer

Master

  • "kratzer" is male

Posts: 92

Date of registration: Feb 23rd 2006

Location: Karlsruhe

Occupation: IT

  • Send private message

4

Sunday, March 25th 2012, 8:42pm

one dimensoin with periods for example:
Year
Qtr. 1
Oct
Nov
Dec
Qtr. 2
Jan
Feb
Mar
Qtr. 3
Apr
May
Jun
Qtr. 4
Jul
Aug
Sep



plus one with single years and elements 2010-2011, 2011-2012 and so son.

scorbett

Master

  • "scorbett" is male

Posts: 67

Date of registration: Nov 4th 2010

Location: Canada

  • Send private message

5

Tuesday, March 27th 2012, 11:09pm

I recently did this for a customer of ours. I first created a Year dimension with the entries:

  1. F2012
  2. F2013
and so on. Then created a Month dimension broken into quarters:
Q1
  • Apr
  • May
  • Jun
Q2
  • Jul
  • Aug
  • Sep
Q3
  • Oct
  • Nov
  • Dec
Q4
  • Jan
  • Feb
  • Mar
Then, in my extract, I map the calendar year and month associated with an event to a fiscal year and month. In the above example, if the calendar month is Apr or greater, then the fiscal year is the calendar year + 1, otherwise the fiscal year equals the calendar year. So, for example, March 2012 gets assigned to F2012 (Q4), but April 2012 gets assigned to F2013 (Q1). Now, when filtering a report, I can show a year dropdown showing fiscal years, and everything is displayed as it should be.

tish1

Sage

Posts: 829

Date of registration: Jul 13th 2009

Location: Vienna / Austria

Occupation: Senior Consultant @ Vector SW DV GmbH

  • Send private message

6

Saturday, March 31st 2012, 10:45am

Hi,

most ERP systems store the data for e.g. March-2012 on 201301 (the period of your fiscal year)
I'd not use the month names as element ids. It's usually easier to import data in a dimension with numeric periods (like 201301, 201302, ... )
You can add the month names via ETL FieldTransform to and a TreeJoin on the right elements if you need that.

Regards.

Rate this thread