If functions, performance

[Anonymous]

Hi All,

I have the idea of developing an app to help me create the commissions statements each month.  I just took this responsibility over for a term'd employee and to be frank, they're a mess.  They are all done in excel, and it's a lot of copy and pasting data.  It's very volatile this way and is very easy to make a mistake. 

I know that I do this in an app - much of the data is stored in salesforce, and the rest I can upload with excel.  The question that I have though is about calculating the %'s or rates in which we pay.   Many of the calculations consist of 10-12 nested if functions.  Factor in those functions, and understanding that those will have to be repeated for several (4-5) different titles, and you can see my fear of it breaking or timing out.

I was just wondering if anyone else has ever tried commissions, or had an idea on how to prevent this?  Can I build the if functions into the script to save performance?  Perhaps work it in excel and upload that way?

Thanks for any feedback!

[marcus_sommer]

In general it's not recommended to use heavily nested if-expressions but it will depend on the amount of data and the overall complexity if the performance is good enough and fits to the expections an/or time-windows respectively system-ressources.

I would try to replace those if-loops or at least to minimize them and often it could be done with mapping and applymap or pick(match()) functions, see here as an example: Re: Substitute to nested ifs

- Marcus

[Anonymous]

Thanks Marcus. Does loading the if's in the script make a dramatic difference? 

At the moment there isn't a TON of data, but through time our database will grow.

[marcus_sommer]

Within the gui are such nested constructions quite often noticeable even if the amount of data isn't very big. Within the script instead you will notice it only then if your time-window is too small or you haven't enough RAM (especially within aggregated loadings will the need of RAM increase heavily). If it's now worked is fine but you will have it as to-do task in the future ...

- Marcus