Most efficient way to deal with an IF on an expression

[#1]

Hi Folks

I am re-working one of my major apps to try to remove huge amounts of IF statements in expressions which are causing some performance issues, I'm doing pretty well on this but have come up with a scenario that you might be able to help with.

I have given the end user the option to select either a quarter end OR a month end for selection of values, the Set Analysis expressions for each are as follows:

=Num ( Sum ( {$ < DT_TransDate = {'<=$(=Only(DT_ReportMonthEnd))'}>} _rCost ) / vDivisor , vNumberFormat )

& for Quarter end selection

=Num ( Sum ( {$ < DT_TransDate = {'<=$(=Only(DT_ReportQuarterEnd))'}>} _rCost ) / vDivisor , vNumberFormat )

Now....

I have created two objects that do the same thing, one is used when a month end is selected, another is used when a quarter end is selected, and I'm now wondering if I might be able to very efficiently combine the two without adding IF statements to the expression.

Note: The DT_ReportMonthEnd and DT_ReportQuarterEnd fields are held in a table that is not directly connected to the remainder of the star schema (DT_TransDate is of course connected to the schema).

Any thoughts?

[Miguel_Angel_Baeyens]

Hello Nigel,

Just a quick read between reloads... I have dealt something similar with variables, storing the dimension I want to work with in a variable, with a default value so the user has not done any selection, and the field is compared to the variable value.

I don't know what objects are you using, but the user selects in a slider/calendar, storing the resulting date in that variable, so the set analysis always compares to a date.

Does this suit you?

[#3]

Hi Miguel

Yes, I think so, I'll play around with that concept a bit and see what happens.

Thanks very much,