TM1 Forum

Hi Folks

Got a question on Performance Modeler (PM) using Picklists and Conditional Calcs. I know I can do this in the Rules editor but really need to explore the limits of PM here


My Measures

Actuals Flag : (picklist showing Actuals in the Actual Months)
Actuals Sales : (Sales data)
Forecast Sales : (Sales data)
Budget Sales : (Sales data):
Revised Forecast : (Calc line)

My Calculation line ‘Revised Forecast’ needs to be [ IF value in ‘Actuals Flag’ = Actuals, THEN take data from ‘Actuals Sales’]

You see, the trouble with PM is that it puts single quotes around variable names which is what I would normally do to reference a text item such as ‘zebras’, ‘potatoes’, or in this case ‘Actuals’.

To make this work I abandoned the idea of referencing the text in the picklist but created more variables(lines) with those options so can use the lines as comparators.

Eg: Created ‘ActualsForced’ line to harcode text ‘Actuals’ in all months then use my real flag ‘Actuals Flag’ as comparator so can compare the values in each line:

=IF 'Actuals Flag' = 'ActualsForced' THEN 'Actuals Sales'

I can aslo assocate each item in a picklist with a number, so Actuals is 1 and Forecast 2. Then in my formula I can say if Flag=1 THEN xyz. This requires building a lookup cube to pull the associated numbers for the picklists of course. Have you guys managed to achieve this ?


Thanks

Mark

Within the performance modeler calculation entry screen you can use double quotes round the string. So the calc you enter is :-

=IF 'Actuals Flag' = "Actuals" THEN 'Actual Sales'

This generates the rule in the rule file


['sales.measures':'Revised Forecast']=N:IF((DB('sales', !regions, !year, 'Actuals Flag')@='Actuals'),['sales.measures':'Actual Sales'],CONTINUE);

@Robins : I cant believe it. Just developed a long winded workaround for this, then read your post. The forum didnt email me your reply.

Your suggestion works! Thank you very much. Furious with myself.

Not finding these tips on the Performance Modeler guides - you have any other sources ?

Thanks

M