negative values...

calculating profit... how do I write an update query that will correct the gross profit calculated column for all negative qty transactions

Basically in the stored procedure that creates the table the query includes:

Profit = CASE Sale WHEN 0 THEN 0 ELSE (Sale - Cost) END,
which is wrong when sale and cost is negative the formula becomes
(-Sale + Cost)... I want it to be -(Sale-Cost) (where sale any cost ignores negative sign....

but i dont know to write this...any ideas?which is wrong when sale and cost is negative the formula becomes
(-Sale + Cost)... I want it to be -(Sale-Cost) (where sale any cost ignores negative sign....

How could 'Cost' come negative..?|||How could 'Cost' come negative..?when the supplier pays you to take his product

next question: how can sale be 0?

answer: when you give your product away|||Sounds like an absolute value to me.

sale - abs(cost)|||calculating profit... how do I write an update query that will correct the gross profit calculated column for all negative qty transactions

Basically in the stored procedure that creates the table the query includes:

Profit = CASE Sale WHEN 0 THEN 0 ELSE (Sale - Cost) END,
which is wrong when sale and cost is negative the formula becomes
(-Sale + Cost)... I want it to be -(Sale-Cost) (where sale any cost ignores negative sign....

but i dont know to write this...any ideas?

Why would it be wrong? Sounds like simple accounting

My wife say I have a lot of negative values|||I worked my way around it... the profit was calculating correct, it was the Profit% that was wrong...soz:

UPDATE SALES
SET [Profit%] = [Profit%] * -1
FROM SALES
WHERE Qty < 0

So that when the profit% is negative when qty is negative... thanks :)|||So that when the profit% is negative when qty is negative... thanks :)you're welcome :)

you sell negative quantities?|||Those are called Returns!|||Those are called Returns!

yup :) :beer:|||Our sales monkeys are good at generating negative GP, too! :D

negative values when calculating percentages

I get negative values for percentage calculation in a MDX query. The MDX query has a crossjoin between two sets containing calculated members from the same dimension, one of the calculated members being a percentage value. I'm not sure why some of the percentage values are negative.

Another problem I'm facing is that the percentage value is not being displayed as per the FORMAT_STRING property, in my Reports in Reporting Services 2005 that use the data generated by the MDX query.

Any help or suggestion is appreciated.

Hi. Can we see the MDX query you're using and a small data sample which provides the negative percentage?

PGoldy

|||

Hope this will give you an idea:

WITH
MEMBER [ITEMA].[ITEMA].itemmember
as

' (ITEMA.ITEMA.&[itemmember], [Measures].[NUMBER]) '

MEMBER [ITEMA].[ITEMA].TOTAL
as

' SUM(ITEMA.ITEMA.Members, [Measures].[NUMBERS]) '

MEMBER [ITEMA].[ITEMA].ITEMPERC
as

' Iif(IsEmpty([ITEMA].[ITEMA].TOTAL),0,([ITEMA].[ITEMA].itemmember / [ITEMA].[ITEMA].TOTAL)) ', FORMAT_STRING = '#.#%'

select [Measures].[NUMBER] on columns,

non empty crossjoin({[ITEMA].[ITEMA].MEMBERS,[ITEMA].[ITEMA].OTHERS,[ITEMA].[ITEMA].itemmember,[ITEMA].[ITEMA].TOTAL,[ITEMA].[ITEMA].ITEMPERC},
crossjoin({ITEMB.ITEMB.children},{ITEMC.ITEMC.children,[ITEMC].[ITEMC].[others]})) on rows
FROM [MyCube]

Data Snapshot:

ITEMA_1 ITEMA_2 ........ ITEMA_itemmember ITEMA_TOTAL ITEMA_ITEMPERC

- ITEMB_1
ITEMC_1 10 20 10 100 -0.1
ITEMC_2 5 6 0 150 0.0
ITEMC_3 0 1 2 4 0.5

- ITEMB_2
ITEMC_1 ...................................................................
ITEMC_2 ...................................................................
ITEMC_3 ...................................................................
+ ITEMB_3
+ ITEMB_4
.
.
.

|||

Hi. Thanks for the detailed query and example.

I don't see why you get a negative percentage, but I see you're using the calculated members to get values which are normally available in the cube without the use of a calculated member when you construct the right query. I think you should reconstruct your query to use the WHERE clause and re-define, and eliminate, some calculated members to get the correct results Here are my recommendations:

(1) Use the WHERE cluase to slice your qeury by the desired measure: WHERE (Measures.Number)

(2) Reference ITEMA dimension on the columns.

(3) Eliminate the following calculated members because they are not needed and we can derive the dsired values from normal intersections in the cube: (a) MEMBER [ITEMA].[ITEMA].itemmember, (b) MEMBER [ITEMA].[ITEMA].TOTAL

(4) Change the definition of MEMBER [ITEMA].[ITEMA].ITEMPERC to reference the correct cube intersections.

Assumption: ITEMA hierarchy has an "all" member, aggregation type for Measures.Number is SUM.

Here's the new query with the recommended changes:

WITH
MEMBER [ITEMA].[ITEMA].ITEMPERC AS
' Iif(IsEmpty([ITEMA].[ITEMA].[All ITEMA]),0,([ITEMA].[ITEMA].CurrentMember / [ITEMA].[ITEMA].[All ITEMA])) ', FORMAT_STRING = '0.0%'

NON EMPTY {[ITEMA].[ITEMA].MEMBERS, [ITEMA].[ITEMA].[All ITEMA], [ITEMA].[ITEMA].ITEMPERC} ON COLUMNS,
crossjoin({ITEMB.ITEMB.children},{ITEMC.ITEMC.children,[ITEMC].[ITEMC].[others]}) on rows
FROM [MyCube]
WHERE ([Measures].[NUMBER])

Hoe this helps.

PGoldy