Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)),
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))
You're missing a close paren on your first DUAL statement: DUAL('<=' &'6.3')
Hi Patrick
I tried that but it doesn't work it just leaves an error from the comma on
Thanks
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))
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
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))
Try this, as DUAL takes two arguments ('display_string', number_value): DUAL('<=6.3',6.3)
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