topic Re: Ranking negative numbers in QlikView

Ranking negative numbers

farheenayesha — Sat, 16 Nov 2024 03:34:18 GMT

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

Re: Ranking negative numbers

dplr-rn — Wed, 15 May 2019 17:13:00 GMT

Whats your rank expression?

Re: Ranking negative numbers

farheenayesha — Wed, 15 May 2019 17:15:52 GMT

Hi Dilip,

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

rank($(+/- Base))

Re: Ranking negative numbers

dplr-rn — Wed, 15 May 2019 17:23:39 GMT

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?

Re: Ranking negative numbers

sunny_talwar — Wed, 15 May 2019 17:25:24 GMT

Can't replicate your issue

Re: Ranking negative numbers

dplr-rn — Wed, 15 May 2019 17:28:55 GMT

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)

Re: Ranking negative numbers

farheenayesha — Wed, 15 May 2019 17:41:44 GMT

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.

Re: Ranking negative numbers

sunny_talwar — Wed, 15 May 2019 17:52:22 GMT

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?

Re: Ranking negative numbers

farheenayesha — Thu, 16 May 2019 15:41:43 GMT

Sorry it was a typo.

I used the below expression

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