You are not logged in.

Dynamic Consolidation across range of elements

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.

blabj

Sage

  • "blabj" is male
  • "blabj" started this thread

Posts: 119

Date of registration: Sep 24th 2009

Location: Canada

  • Send private message

1

Tuesday, November 24th 2009, 9:46pm

Dynamic Consolidation across range of elements

I'm building a spreadsheet that allows the user to select a "From" yr/month and a "To" yr/month (time dimension) and allowing up to a 12 month selection, and generating "Current" and "LY" sales figures.

I am able to accomplish it by building two 12 column month-by-month lookups, and performing sums, but is there a way to do dynamic consolidation across a range of elements in a single cell?

Something like: PALO.DATA($A$1;... ;"2009.Jan:2009.Jul";...)

Creating a subset via modeller doesn't seem to be an option as they need to be explicitly defined (not dynamic) from what I can see.

-Bob

h_decker

Sage

  • "h_decker" is male

Posts: 763

Date of registration: Oct 26th 2005

Location: Cologne, Germany

  • Send private message

2

Wednesday, November 25th 2009, 11:05am

RE: Dynamic Consolidation across range of elements

Bob,
perhaps there is someone who can up up with a pure palo solution for that issue.

I myself think that it is absolutely okay having simple things like building the sum for the rolling 12 months (palo source) done by Excel.

Holger

introibo

Master

  • "introibo" is male

Posts: 43

Date of registration: Feb 17th 2006

Location: Germany

  • Send private message

3

Wednesday, November 25th 2009, 11:40am

You could create consolidations for each possible From-To pair. For a whole year, there are only 66 8)
Lot of typing at first, but then you're done.

Uwe

h_decker

Sage

  • "h_decker" is male

Posts: 763

Date of registration: Oct 26th 2005

Location: Cologne, Germany

  • Send private message

4

Wednesday, November 25th 2009, 12:56pm

but there is another challange ;-)
What if there's a year change in between (e.g. Dec 2009 to Nov 2010)...

introibo

Master

  • "introibo" is male

Posts: 43

Date of registration: Feb 17th 2006

Location: Germany

  • Send private message

5

Wednesday, November 25th 2009, 2:38pm

It's an arithmetic sequence. So the math is simple: Sn = n*(n+1)/2

For one year, n is 11 and Sn = 66
For two years, n is 23 and Sn = 276
To cover three years: n=35, Sn=630
....
:D

(You could write some VBA code to create the consolidations)

blabj

Sage

  • "blabj" is male
  • "blabj" started this thread

Posts: 119

Date of registration: Sep 24th 2009

Location: Canada

  • Send private message

6

Wednesday, November 25th 2009, 5:30pm

RE: Dynamic Consolidation across range of elements

Thanks for the suggestions.. at this point the simplest route is using spreadsheet SUM.. I've built it in such a way that it can span years too. PALO.EINDEX is very helpful in combination with PALO.ENEXT.. since ENEXT loops within one level (eg. month), you can check EINDEX to see when it loops back to Jan, and force a jump to next year.

I just though there might be an SQL-like method directly in Palo.

Thanks again,

-Bob
Rate this thread