Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
farheenayesha
Contributor

Ranking negative numbers

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.

RegionSales $Base $+/-  Base $National Rank
Region 1320481360061($39,580)12
Region 2139249154504($15,255)10
Region 3190005181575$8,4308
Region 41099089892859$206,2301
Region 5691091613296$77,7952
Region 61007884979998$27,8866
Region 7487865443134$44,7314
Region 8288525287255$1,2709
Region 9392518377699$14,8197
Region 10551426496327$55,0993
Region 11980422951893$28,5295
Region 12356337364588($8,251)11

 

Please help me to get the below results.

RegionSales $Base $+/-  Base $National Rank
Region 1320481360061($39,580)12
Region 2139249154504($15,255)11
Region 3190005181575$8,4308
Region 41099089892859$206,2301
Region 5691091613296$77,7952
Region 61007884979998$27,8866
Region 7487865443134$44,7314
Region 8288525287255$1,2709
Region 9392518377699$14,8197
Region 10551426496327$55,0993
Region 11980422951893$28,5295
Region 12356337364588($8,251)10

 

@sunny_talwar 

Labels (1)
2 Solutions

Accepted Solutions
Highlighted
Partner
Partner

Re: Ranking negative numbers

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

Capture.PNG

Is there any information that you are not sharing?

View solution in original post

Highlighted

Re: Ranking negative numbers

Can't replicate your issue

image.png

View solution in original post

8 Replies
Highlighted
Partner
Partner

Re: Ranking negative numbers

Whats your rank expression?
Highlighted
farheenayesha
Contributor

Re: Ranking negative numbers

Hi Dilip,

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

 

rank($(+/- Base))

Highlighted
Partner
Partner

Re: Ranking negative numbers

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

Capture.PNG

Is there any information that you are not sharing?

View solution in original post

Highlighted

Re: Ranking negative numbers

Can't replicate your issue

image.png

View solution in original post

Highlighted
Partner
Partner

Re: Ranking negative numbers

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)
Highlighted
farheenayesha
Contributor

Re: Ranking negative numbers

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.

 

 

Highlighted

Re: Ranking negative numbers

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? 

Highlighted
farheenayesha
Contributor

Re: Ranking negative numbers

Sorry it was a typo.

I used the below expression

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