Ranking negative numbers

[farheenayesha]

Hi,

I am trying to rank the +/- Base value which contains negative numbers as well. For Region 2 the should show as 11 not 10.

Region	Sales $	Base $	+/-  Base $	National Rank
Region 1	320481	360061	($39,580)	12
Region 2	139249	154504	($15,255)	10
Region 3	190005	181575	$8,430	8
Region 4	1099089	892859	$206,230	1
Region 5	691091	613296	$77,795	2
Region 6	1007884	979998	$27,886	6
Region 7	487865	443134	$44,731	4
Region 8	288525	287255	$1,270	9
Region 9	392518	377699	$14,819	7
Region 10	551426	496327	$55,099	3
Region 11	980422	951893	$28,529	5
Region 12	356337	364588	($8,251)	11

Please help me to get the below results.

Region	Sales $	Base $	+/-  Base $	National Rank
Region 1	320481	360061	($39,580)	12
Region 2	139249	154504	($15,255)	11
Region 3	190005	181575	$8,430	8
Region 4	1099089	892859	$206,230	1
Region 5	691091	613296	$77,795	2
Region 6	1007884	979998	$27,886	6
Region 7	487865	443134	$44,731	4
Region 8	288525	287255	$1,270	9
Region 9	392518	377699	$14,819	7
Region 10	551426	496327	$55,099	3
Region 11	980422	951893	$28,529	5
Region 12	356337	364588	($8,251)	10

@sunny_talwar 

[dplr-rn]

Whats your rank expression?

[farheenayesha]

Hi Dilip,

Thanks for the quick reply. Please find the expression below.

rank($(+/- Base))

[dplr-rn · Accepted solution]

Not sure what the problem is. i loaded the data from your post.

Added Region as dimension others as measures then rank. see below screenshot

Is there any information that you are not sharing?

[sunny_talwar · Accepted solution]

Can't replicate your issue

[dplr-rn]

I am taking a wild guess that you have other dimensions involved in your actual app. if so use something like below
Aggr(Rank(total [+/-  Base $]),Region)

[farheenayesha]

Thanks Dilip and Sunny. 

Problem was in the script.

Sales_Goal:
Load
ALGN_STRUC_NM&OPTMZR_SLS_ALGN_HIST_REP_NM&COMM_CAT&TERR_ID&DIV_NM&REG_NM&FORECAST_GRP as Key,
'Sales' as Flag,
ALGN_STRUC_NM,
OPTMZR_SLS_ALGN_HIST_REP_NM,
COMM_CAT ,

COMM_SUBCAT,
TERR_ID,
DIV_NM,
REG_NM,
FORECAST_GRP,
BASE_AMT as [Base],
SLS_AMT as [Sales]
FROM [lib://Fire & Ice/Totem_Sales.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq)
;

Concatenate
//[Totem_Goal]:
Load
[Rep Type]&[Sales Repname]&[Comm Cat Desc]&[Terr Id No]&[Div Desc]&[Reg Desc]&[Forecast Grp] as Key,
'Goal' as Flag,
[Rep Type] as ALGN_STRUC_NM,
[Comm Cat Desc] as COMM_CAT_Old,
// [Comm Cat Desc] as COMM_CAT,
[Sales Repname] as OPTMZR_SLS_ALGN_HIST_REP_NM,
[Terr Id No] as TERR_ID,
[Div Desc] as DIV_NM,
[Reg Desc] as REG_NM,
[Forecast Grp] as FORECAST_GRP,
[Sales],
[Base],
[+/- Over Goal]
FROM [lib://Fire & Ice/Totem_Goal.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq)
;

I had two fact tables, hence i had concatenated both the tables and created a common field called flag. For sales and Base, i was using below exp.

Sum({<Flag='Sales'> Sales}

Sum({<Flag='Sales'> Base}

After commenting second fact table, the rank is showing correctly.

Please let me know, if you guys know the reason. Why after removing concatenation,  the rank is showing up correctly.

[sunny_talwar]

These expressions look wrong to me and should not give any result

Sum({<Flag='Sales'> Sales}
Sum({<Flag='Sales'> Base}

Are you sure these are the expressions your used? 

[farheenayesha]

Sorry it was a typo.

I used the below expression

sum({<Flag={'Sales'}>} Sales)-sum({<Flag={'Sales'}>} Base)