Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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'
HI,
you should have square brackets [] around the field names in the load statement that have spaces in then
HTH Andy
YES I USED SQUARE BRACKETS ONLY BUT STILL I FIND NEGATIVE VALUE
Can you upload your QVW please?
Andy
You can use Fab() function to convert the negative values to positive values.
Have you checked if the value is negative in your source data?
Andy
is there a reason that you don't do this in the where clause
Where Period >='201503' AND
[MAT N-1 Actual] >0
where to use fab condition.can you please say me
yes in the source it is negative
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