Reg-Negative value in Target

[#1]

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'

[awhitfield]

HI,

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

HTH Andy

[#3]

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

[awhitfield]

Can you upload your QVW please?

Andy

[Siva_Sankar]

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

[awhitfield]

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

Andy

[#7]

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

Where Period >='201503' AND

[MAT N-1 Actual] >0

[#8]

where to use fab condition.can you please say me

[#9]

yes in the source it is negative

[awhitfield]

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