CODEWITHSUNDEEP
sql-servermdx
daniel_aren
daniel_aren

Reputation: 1924

iif mdx result should be negative

I want this result to show a negative when the change is negative but it looks like mdx are taking the absolute result and always shows positive numbers. Its the third column i want to make negative when the result is negative in the third member:

member [Measures].[Change in Reseller Sales Percent]

i think i can do it with a iif - statement but how?

code:

with
member [Measures].[Prev Month Reseller Sales Amount] as
    ([Date].[Calendar].currentmember.prevmember,
    [Measures].[Reseller Sales Amount])
    ,format_string="currency"

member [Measures].[Change in Reseller Sales] as
    ([Measures].[Reseller Sales Amount]-
    [Measures].[Prev Month Reseller Sales Amount])

member [Measures].[Change in Reseller Sales Percent] as
    iif([Date].[Calendar].currentmember.prevmember is null, null,
        ([Measures].[Change in Reseller Sales])/ 
    ([Measures].[Prev Month Reseller Sales Amount]))
    , format_string = "percent"
SELECT
 {([Measures].[Reseller Sales Amount]),
 ([Measures].[Prev Month Reseller Sales Amount]),
 ([Measures].[Change in Reseller Sales]),
 ([Measures].[Change in Reseller Sales Percent])} ON COLUMNS,
 {[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]

RESULT:

September 2001  $1,165,897.08   $1,538,408.31   ($372,511.23)   -24.21%
October 2001    $844,721.00 $1,165,897.08   ($321,176.08)   -27.55%
November 2001   $2,324,135.80   $844,721.00 $1,479,414.80   175.14%
December 2001   $1,702,944.54   $2,324,135.80   ($621,191.25)   -26.73%
January 2002    $713,116.69 $1,702,944.54   ($989,827.85)   -58.12%

Upvotes: 2

Views: 764

Answers (1)

Emiliano Poggi
Emiliano Poggi

Reputation: 24826

You should specify the format specifier for the negative numbers in the format_string. Example

    format_string "$#,##0;-$#,##0"

Upvotes: 1

Related Questions