topic Re: Dual expression help needed in QlikView

Dual expression help needed

rcorcoran — Mon, 30 Apr 2018 15:08:07 GMT

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)),

Re: Dual expression help needed

Anonymous — Mon, 30 Apr 2018 15:11:06 GMT

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

Re: Dual expression help needed

rcorcoran — Mon, 30 Apr 2018 15:13:46 GMT

Hi Patrick

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

Thanks

Re: Dual expression help needed

sunny_talwar — Mon, 30 Apr 2018 15:27:06 GMT

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))

Re: Dual expression help needed

rcorcoran — Mon, 30 Apr 2018 15:30:17 GMT

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

Re: Dual expression help needed

sunny_talwar — Mon, 30 Apr 2018 15:33:32 GMT

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))

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))

Re: Dual expression help needed

Anonymous — Mon, 30 Apr 2018 15:34:47 GMT

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

Re: Dual expression help needed

rcorcoran — Mon, 30 Apr 2018 15:38:40 GMT

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