topic Re: Reg-Negative value in Target in QlikView

Reg-Negative value in Target

Fri, 22 May 2015 07:51:50 GMT

Hi team

we have created the straight table with the dim as TA NAME,BRAND,PACKAGE.

we have written expressions like

Actual=sum(actual)

target=sum(target)

N-1 actual=sum(n-1 actual)

YTD ACTUAL=Sum(ytd actual)

YTD TARGET=sum(YTD target)

but we are getting negative value in target..if select brand-=essen and Bu=cvim and year=201504. i want the positive value in target.

Below is the script.can any one help me out from this.its very urgent.

SELECT Actual,

AreaName,

Brand,

BrandName,

CASE WHEN AreaName LIKE '%CVIM-Essentiale%'

THEN 'Essentiale Hospital' else BU end as BU,

Category,

"MAT Actual",

"MAT N-1 Actual",

"MAT Target",

"N-1 Actual",

Package,

Period,

PriceType,

ProvinceE,

RSMHC,

RSMHCID,

TAName,

Target,

"YTD Actual",

"YTD N-1 Actual",

"YTD Target"

FROM "Rx_BI_Qlikview".dbo."dataset_complete_report"

where Period >='201503'

Re: Reg-Negative value in Target

awhitfield — Fri, 22 May 2015 07:58:47 GMT

HI,

you should have square brackets [] around the field names in the load statement that have spaces in then

HTH Andy

Re: Reg-Negative value in Target

Fri, 22 May 2015 08:33:41 GMT

YES I USED SQUARE BRACKETS ONLY BUT STILL I FIND NEGATIVE VALUE

Re: Reg-Negative value in Target

awhitfield — Fri, 22 May 2015 08:53:44 GMT

Can you upload your QVW please?

Andy

Re: Reg-Negative value in Target

Siva_Sankar — Fri, 22 May 2015 09:37:56 GMT

You can use Fab() function to convert the negative values to positive values.

Re: Reg-Negative value in Target

awhitfield — Fri, 22 May 2015 09:45:32 GMT

Have you checked if the value is negative in your source data?

Andy

Re: Reg-Negative value in Target

Fri, 22 May 2015 10:21:34 GMT

is there a reason that you don't do this in the where clause

Where Period >='201503' AND

[MAT N-1 Actual] >0

Re: Reg-Negative value in Target

Fri, 22 May 2015 10:41:10 GMT

where to use fab condition.can you please say me

Re: Reg-Negative value in Target

Fri, 22 May 2015 10:41:34 GMT

yes in the source it is negative

Re: Reg-Negative value in Target

awhitfield — Fri, 22 May 2015 10:53:56 GMT

Ok,

Apply fabs() to the relevant target fields as you have multiple targets in the script e.g.

sum(fabs(target)) as target

HTH Andy