topic Re: calculation not working with hidden column or with symbol atted in QlikView

calculation not working with hidden column or with symbol atted

rcorcoran — Thu, 11 Aug 2016 13:07:01 GMT

I am using this expression in a pivot table to show a value. I have the '<' in as I need the target to show as <5% etc. This will show fine in the column I am using this expression in but I am also using the column to calculate a variance and it will not work because of this '<'

Expression in my Target YTD Column that i use for variance calculation below also:

if(ReferredYYYY=2016,'<' & num(avg({$<TargetCode={'PC31'},TargetReferredYYYY={2016}, TargetReferredMM={"<=$(=vCurrentMonth)"}>}[Target])/100,'##0%'),

Variance Calculation on columns:

([Reported

Activity YTD] - [Target YTD Calc])/[Target YTD Calc],

I have tried to put it in another column without the '<' and hide this column but when I hide the column the expression in the variance will not work.

Any Help would be appreciated

Re: calculation not working with hidden column or with symbol atted

sunny_talwar — Thu, 11 Aug 2016 13:16:44 GMT

May be use a dual function for the column:

If(ReferredYYYY=2016,

Dual('<' & Num(Avg({$<TargetCode={'PC31'},TargetReferredYYYY={2016}, TargetReferredMM={"<=$(=vCurrentMonth)"}>}[Target])/100,'##0%'),

Avg({$<TargetCode={'PC31'},TargetReferredYYYY={2016}, TargetReferredMM={"<=$(=vCurrentMonth)"}>}[Target])/100))

Re: calculation not working with hidden column or with symbol atted

rcorcoran — Fri, 12 Aug 2016 10:29:21 GMT

Thank you so much Sunny this works fine

Rhona