If else or set analysis?

[philip_radovich]

Hi,

I have the following script to calculate the percentage change based on performance item and assessment dates.

(FirstSortedValue(Value/100, -AssessmentDate)-FirstSortedValue(Value/100, AssessmentDate))/FirstSortedValue(Value/100, AssessmentDate). This works for the most part but different values are based on different units of measure. I have a column in my database that is called Reverse which is a 1 or a 0 based on whether the unit of measure is a time or a count of the performance item. I am struggling to write a script in the app that can calculate this percentage change and display the correct results based on the Reverse column. Any help would be greatly appreciated.

Thank you,

Philip Radovich

[Gysbert_Wassenaar]

You can't use set analysis in the script. And you want to branch between two possible execution paths which also can't be done with set analysis. So you need to use and if(...) statement.

If(Reverse=1, interval( ....something...., 'hh:mm:ss'), num( ...something ... , '#,##0.00'))