Dual expression help needed

[rcorcoran]

Hi I need help with the expression below.  I need it to show <=6/3% if Dimensionality =0 otherwise the targets I have loaded.  The two parts work separately but together don't I think I am missing a bracket somewhere??

Any help appreciated

IF(Dimensionality()=0, DUAL('<=' &'6.3',
DUAL('<=' & NUM(AVG( {$<TargetCode={'CPA11'}, TargetReferredYYYY={$(vnp_YearCurrentMonthMinus1)}, TargetReferredMM={$(vnp_MaxReferredMMMinus1)}, ReferredMM=, YearMonth=>}  Target), format.decimal1),
AVG( {$<TargetCode={'CPA11'}, TargetReferredYYYY={$(vnp_YearCurrentMonthMinus1)}, TargetReferredMM={$(vnp_MaxReferredMMMinus1)}, ReferredMM=, YearMonth=>}  Target)),

[Anonymous]

You're missing a close paren on your first DUAL statement: DUAL('<=' &'6.3')

[rcorcoran]

Hi Patrick

I tried that but it doesn't work it just leaves an error from the comma on

Thanks

[sunny_talwar]

May be this

If(Dimensionality() =0, '<= 6.3', Dual('<=' & Num(Avg({$<TargetCode={'CPA11'}, TargetReferredYYYY ={$(vnp_YearCurrentMonthMinus1)}, TargetReferredMM = {$(vnp_MaxReferredMMMinus1)}, ReferredMM, YearMonth>} Target), format.decimal1), Avg({$<TargetCode = {'CPA11'}, TargetReferredYYYY = {$(vnp_YearCurrentMonthMinus1)}, TargetReferredMM = {$(vnp_MaxReferredMMMinus1)}, ReferredMM, YearMonth>} Target))

[rcorcoran]

Hi Sunny

I need the first part to be a dual expression also as I am calculating a variance based on the figure of <=6.3 in another column and need it without the <= for this to work.

thanks

[sunny_talwar · Accepted solution]

How about this

If(Dimensionality() =0, Dual('<= 6.3', 6.3), Dual('<=' & Num(Avg({$<TargetCode={'CPA11'}, TargetReferredYYYY ={$(vnp_YearCurrentMonthMinus1)}, TargetReferredMM = {$(vnp_MaxReferredMMMinus1)}, ReferredMM, YearMonth>} Target), format.decimal1), Avg({$<TargetCode = {'CPA11'}, TargetReferredYYYY = {$(vnp_YearCurrentMonthMinus1)}, TargetReferredMM = {$(vnp_MaxReferredMMMinus1)}, ReferredMM, YearMonth>} Target))

or

If(Dimensionality() =0, Dual('<= 6.3', 0.063), Dual('<=' & Num(Avg({$<TargetCode={'CPA11'}, TargetReferredYYYY ={$(vnp_YearCurrentMonthMinus1)}, TargetReferredMM = {$(vnp_MaxReferredMMMinus1)}, ReferredMM, YearMonth>} Target), format.decimal1), Avg({$<TargetCode = {'CPA11'}, TargetReferredYYYY = {$(vnp_YearCurrentMonthMinus1)}, TargetReferredMM = {$(vnp_MaxReferredMMMinus1)}, ReferredMM, YearMonth>} Target))

[Anonymous]

Try this, as DUAL takes two arguments ('display_string', number_value): DUAL('<=6.3',6.3)

[rcorcoran]

Hi Sunny

The first one above works with just an extra bracket at the end of it.  Thanks so much it was driving me mad for the last hour or so.

Patrick thanks also for your help with this.

Regards