You are not logged in.

Problem with Palo.datac ==> No double calculation

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.

DSTeamLaf

Intermediate

  • "DSTeamLaf" started this thread

Posts: 4

Date of registration: Sep 24th 2010

  • Send private message

1

Friday, September 24th 2010, 3:14pm

Problem with Palo.datac ==> No double calculation

Good afternoon,

I have on an excel workbook, different type of Palo function: Palo.ename , palo.data , palo.datac...

Today, when I opened my workbook, all the functions worked well except the palo.datac function which display: #N/A
Yesterday it worked well, all the functions sent back all the values I needed.

I noticed today that Excel only calculate once. It is supposed to calculate twice because of the palo.datac function. Yesterday, it calculated twice and I don't understand why it's not working today!

An idea?

tish1

Sage

Posts: 761

Date of registration: Jul 13th 2009

Location: Vienna / Austria

Occupation: Senior Consultant @ Vector SW DV GmbH

  • Send private message

2

Saturday, September 25th 2010, 4:14pm

Hi,

if other cells reference your datac formula, you should use data instead.
At least that's what the manual says. Does this help you?

regards.
Robert Tischler
Senior Consultant Vector SW DV GmbH

DSTeamLaf

Intermediate

  • "DSTeamLaf" started this thread

Posts: 4

Date of registration: Sep 24th 2010

  • Send private message

3

Monday, September 27th 2010, 3:16pm

Hi,

it's working with palo.data but I seems that palo or Excel doesn't like when there is too many formula (more than 6000). It crashs when I ask Excel to calculate (F9)

holger_b

Sage

  • "holger_b" is male

Posts: 271

Date of registration: Jun 1st 2010

Location: Freiburg, Germany

Occupation: BI Consultant

  • Send private message

4

Tuesday, September 28th 2010, 9:34am

Hi DSTeamLaf,

more than 6000 cells sounds like you should try with DATAV() unless you want to enter data.

Is this a real life scenario, or are you just trying what happens? Why would anyone want to see 6000 figures on one sheet?

Regards
Holger

DSTeamLaf

Intermediate

  • "DSTeamLaf" started this thread

Posts: 4

Date of registration: Sep 24th 2010

  • Send private message

5

Wednesday, September 29th 2010, 11:17am

Hi Holger,

I have more than 6000 cells in my workbook, but when you use F9, it calculates all the cells in the workbook.

holger_b

Sage

  • "holger_b" is male

Posts: 271

Date of registration: Jun 1st 2010

Location: Freiburg, Germany

Occupation: BI Consultant

  • Send private message

6

Wednesday, September 29th 2010, 11:45am

Hi,

good to hear it does, since actually it should :-). I was just wondering why one might need that.

Are you familiar with PALO.DATAV()? Really good to use with vast lists like yours.

Regards
Holger

jgibbs

Sage

  • "jgibbs" is male

Posts: 154

Date of registration: Dec 5th 2006

Location: Toronto

Occupation: Accountant

  • Send private message

7

Wednesday, September 29th 2010, 5:48pm

I have a report that tracks about 100 sales reps and their performance selling 7 categories on a weekly, cycle and annual basis. There's about 21,000 palo links on my data tab which then feeds individual tabs for each rep. I have a macro that emails each individual tab. Each week I change a couple parameters and use f9 to update all the data and tabs.

Anyway, I always use data.c without any problem but decided to experiment with the other types. Here's what I found for performance (hitting f9 to update the datasheet):

palo.data - 38 seconds
palo.datav - 45 seconds
palo.datac - 6 seconds

v_malicevic

Palo Team

  • "v_malicevic" is male

Posts: 454

Date of registration: Oct 26th 2005

Location: Germany

  • Send private message

8

Wednesday, September 29th 2010, 9:21pm

Quoted

Originally posted by jgibbs
There's about 21,000 palo links on my data tab

By palo links you mean cells with palo related formulas?

Quoted


palo.data - 38 seconds
palo.datav - 45 seconds
palo.datac - 6 seconds

45 is very strange. Are you sure you are using DATAV in proper manor(as Excel array formula)?
Mit freundlichen Gruessen/ With kind Regards / Meilleures salutations

Vladislav Malicevic
Head of Research and Development

Jedox AG

jgibbs

Sage

  • "jgibbs" is male

Posts: 154

Date of registration: Dec 5th 2006

Location: Toronto

Occupation: Accountant

  • Send private message

9

Thursday, September 30th 2010, 4:57pm

Right on both counts. For the test I replaced palo.datac with palo.datav (using search and replace). I didn't realize it was an array function. Whoops!

v_malicevic

Palo Team

  • "v_malicevic" is male

Posts: 454

Date of registration: Oct 26th 2005

Location: Germany

  • Send private message

10

Thursday, September 30th 2010, 5:45pm

OK, so when you do it properly with array formulas, does it get better?
Mit freundlichen Gruessen/ With kind Regards / Meilleures salutations

Vladislav Malicevic
Head of Research and Development

Jedox AG

jgibbs

Sage

  • "jgibbs" is male

Posts: 154

Date of registration: Dec 5th 2006

Location: Toronto

Occupation: Accountant

  • Send private message

11

Friday, October 1st 2010, 5:42pm

It may take me a while to figure this out. I don't know how the formula works. I'll try to get to it next week as I'm really curious to see the effect.

holger_b

Sage

  • "holger_b" is male

Posts: 271

Date of registration: Jun 1st 2010

Location: Freiburg, Germany

Occupation: BI Consultant

  • Send private message

12

Wednesday, October 6th 2010, 10:58am

You can see an example of the syntax by creating a view in the Palo Paste View dialogue, choosing the option PALO.DATAV(). You will see that there is just one formula across the entire table which contains arrays (i.e. parameter lists) for both column and row headers. You can see a formula is an array formula when it is enclosed in curly brackets.

To do that manually in your application, highlight the area to which you want to apply your DATAV() formula, type (or modify) your formula and save it pressing CTRL-Shift+Enter.

Have fun
Holger
Rate this thread