topic Re: how to show the Previous Rank in straight table. in QlikView

how to show the Previous Rank in straight table.

rkpatelqlikview — Thu, 26 Apr 2018 14:01:40 GMT

Hi Experts,

Please help on this.

Below is my expression in in straight table for Rank.

Aggr(

rank(

(Round(sum({<[Safety Category]={'Green'}>} [Kms Travelled]) / sum([Kms Travelled]) *100)/4)

+(Round((COUNT(DISTINCT [DEVICE NO]) -COUNT([Device Number]))/COUNT(DISTINCT [DEVICE NO]) *100 )/4 ) +

(Round(SUM({<controlled={'Yes'}>}Quantity)/SUM({<controlled={'Yes'}+{'No'}>}Quantity)*100)/4)

(Round(SUM({<[iVMS ( Y/N)]={'Yes'}>}Quantity)/SUM({< [iVMS ( Y/N)]={'Yes'}+{'No'}>}Quantity)*100)/4)+sum([Kms Travelled])/(1E10)

,4,1),Group, [Carrier Name])

If i select this month in other expression it should be Previous Rank..

Please let me know if you want more info.

Re: how to show the Previous Rank in straight table.

sunny_talwar — Thu, 26 Apr 2018 14:05:25 GMT

Is there a sample you would be able to share you help you better here?

Re: how to show the Previous Rank in straight table.

bc-thebruuu — Thu, 26 Apr 2018 14:13:10 GMT

Usually rank function needs TOTAL to work properly

Re: how to show the Previous Rank in straight table.

rkpatelqlikview — Thu, 26 Apr 2018 14:21:16 GMT

Thanks Sunny and Bruno,

Please find the attached and There is total points but Rank is getting Nulls instead of ranking. I need to show that rank as well.

Re: how to show the Previous Rank in straight table.

bc-thebruuu — Thu, 26 Apr 2018 14:27:26 GMT

The point is that sum([Kms Travelled]) is null.

Your rank function looks good but it cannot rank 'divided by zero' values

What do you expect to happen when your rank function elements are divided by zero?

You have to use Rangesum probably

instead of rank of (sum...+ sum+ sum) you have to use rank(rangesum(sum1, sum2, sum3)

Remember that 1+ null= null

Re: how to show the Previous Rank in straight table.

sunny_talwar — Thu, 26 Apr 2018 14:28:21 GMT

Try using RangeSum() instead of + just like you did for Total Points

Re: how to show the Previous Rank in straight table.

sunny_talwar — Thu, 26 Apr 2018 14:38:34 GMT

Also suggest using formatting from number's tab rather than using '%' for your KPI expression... did the change from KPI1 and KPI2

Re: how to show the Previous Rank in straight table.

rkpatelqlikview — Thu, 26 Apr 2018 14:45:47 GMT

Thanks Sunny. Will check it out accordingly.

Re: how to show the Previous Rank in straight table.

rkpatelqlikview — Thu, 26 Apr 2018 14:51:31 GMT

Thats fine Sunny.

How to show the last Rank of that particular CarrierName?

there is a Expression name Previous rank?

Please may i know how to do?

Re: how to show the Previous Rank in straight table.

rkpatelqlikview — Thu, 26 Apr 2018 14:52:00 GMT

Many thanks Bruno.

How to show the last Rank of that particular CarrierName?

there is a Expression name Previous rank?

Please may i know how to do?

Re: how to show the Previous Rank in straight table.

sunny_talwar — Thu, 26 Apr 2018 14:53:23 GMT

Not sure I understand... can you pin point the exact issue? May be provide the exact number you are looking to get?

Re: how to show the Previous Rank in straight table.

bc-thebruuu — Thu, 26 Apr 2018 14:56:41 GMT

Last Rank = count

Am I right?

Can yo provide an expected result?

Re: how to show the Previous Rank in straight table.

rkpatelqlikview — Thu, 26 Apr 2018 15:10:39 GMT

Sure Sunny / Bruno,

Please find the below 2 images. Here CarierName "Sainath Transporter" is Rank 1.

Now i selected the month March then that "Sainath Transporter" is Rank is 7. This Rank it should show in Previous Rank column.

Re: how to show the Previous Rank in straight table.

bc-thebruuu — Thu, 26 Apr 2018 15:21:41 GMT

Is previous wrt to previous selection or previous month?

Re: how to show the Previous Rank in straight table.

sunny_talwar — Thu, 26 Apr 2018 15:22:27 GMT

Would you be able to share a sample where Jan have some data?

Re: how to show the Previous Rank in straight table.

sunny_talwar — Thu, 26 Apr 2018 15:23:10 GMT

I believe previous month... so when March is selected, show Feb's Rank in Previous Rank

Re: how to show the Previous Rank in straight table.

rkpatelqlikview — Thu, 26 Apr 2018 15:25:45 GMT

Yes Exactly. Previous month

Re: how to show the Previous Rank in straight table.

rkpatelqlikview — Thu, 26 Apr 2018 15:26:11 GMT

sure

Re: how to show the Previous Rank in straight table.

bc-thebruuu — Thu, 26 Apr 2018 15:30:03 GMT

Anyway ..

I think that Overall Rank should contain in each set analysis a constraint on

MonthYear to be max possible month

and previous montth should be max possible month -1

Re: how to show the Previous Rank in straight table.

rkpatelqlikview — Thu, 26 Apr 2018 15:41:24 GMT

I need to write an expression for month. There is Quarter and Year as well.