topic Re: How to show RANK in Text object in QlikView

How to show RANK in Text object

rkpatelqlikview — Fri, 09 Mar 2018 07:14:54 GMT

Hi All,

Can anyone please explain how to show the rank in text object. When i selected the name in the selection box then the Current Rank it should display in one Text Object and and previous rank in one more text object.

Below is the formula for ranking which is i used in Straight Table.

Thanks

KUMAR

Re: How to show RANK in Text object

YoussefBelloum — Fri, 09 Mar 2018 09:06:06 GMT

Hi,

you forgot to put the formula

Re: How to show RANK in Text object

rkpatelqlikview — Wed, 14 Mar 2018 07:05:42 GMT

Hi Youssef,

AM very sorry for that. Please find the below formula for KPI and RAnk.

KPI% Formula

=((Ceil(SUM({<[Control_Fleet (Y/N)]={'Yes'}>}Quantity)/SUM({<[Control_Fleet (Y/N)]={'Yes'}+{'No'}>}Quantity)*100,0.01,0) /4)

(Ceil(SUM({<[iVMS ( Y/N)]={'Yes'}>}Quantity)/SUM({<[iVMS ( Y/N)]={'Yes'}+{'No'}>}Quantity)*100,0.1,0)/4)

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

(round((Count(DISTINCT [Vehicle Number]) - Count(DISTINCT {<Status1 = {"$(='>=' & Date(Today() - 7))"}>} [Vehicle Number]))/Count (DISTINCT [Vehicle Number]) *100, 0.01,0)/4))

Rank:

=aggr (rank(TOTAL rangesum((Ceil(SUM({<[Control_Fleet (Y/N)]={'Yes'}>}Quantity)/SUM({<[Control_Fleet (Y/N)]={'Yes'}+{'No'}>}Quantity)*100,0.01,0) /4)

(Ceil(SUM({<[iVMS ( Y/N)]={'Yes'}>}Quantity)/SUM({<[iVMS ( Y/N)]={'Yes'}+{'No'}>}Quantity)*100,0.1,0)/4 )

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

(round((Count(DISTINCT [Vehicle Number]) - Count(DISTINCT {<Status1 = {"$(='>=' & Date(Today() - 7))"}>} [Vehicle Number]))/Count (DISTINCT [Vehicle Number]) *100, 0.01,0)/4)), 1), [Carrier Name])

Thanks for your support

Re: How to show RANK in Text object

YoussefBelloum — Wed, 14 Mar 2018 09:27:41 GMT

Try this:

Rank on a textbox:

=Aggr(

Rank(

((Ceil(SUM({<[Control_Fleet (Y/N)]={'Yes'},[Carrier Name]>}Quantity)/SUM({<[Control_Fleet (Y/N)]={'Yes'}+{'No'},[Carrier Name]>}Quantity)*100,0.01,0) /4)

(Ceil(SUM({<[iVMS ( Y/N)]={'Yes'},[Carrier Name]>}Quantity)/SUM({<[iVMS ( Y/N)]={'Yes'}+{'No'},[Carrier Name]>}Quantity)*100,0.1,0)/4)

(ceil(sum({<[Safety Category]={'Green'},[Carrier Name]>} [Kms Travelled])/sum({<[Carrier Name]>}[Kms Travelled]) *100,0.1,0) /4 )

(round((Count( {<[Carrier Name]>} DISTINCT [Vehicle Number]) - Count(DISTINCT {<Status1 = {"$(='>=' & Date(Today() - 7))"},[Carrier Name]>} [Vehicle Number]))/Count ( {<[Carrier Name]>} DISTINCT [Vehicle Number]) *100, 0.01,0)/4))

), [Carrier Name])

Re: How to show RANK in Text object

rkpatelqlikview — Wed, 14 Mar 2018 09:33:22 GMT

Many Thanks Youssef, Really helped me a lot.

Can i get previous rank also of the CarrierName?

Please how can i do?

Re: How to show RANK in Text object

YoussefBelloum — Wed, 14 Mar 2018 10:13:47 GMT

what do you mean by previous Rank ?

example, you make a selection on the Carrier name and with the expression above you have the Rank 8

previous rank will give you 7 ?

Re: How to show RANK in Text object

rkpatelqlikview — Wed, 14 Mar 2018 11:00:37 GMT

for example the carrier name Previous rank is 6 based on the data and current rank is 2.

Till now we found the expression for Current rank so that its showing current rank, i.e., 2.

same time I need to show previous rank 6 as well.

Re: How to show RANK in Text object

YoussefBelloum — Wed, 14 Mar 2018 11:12:14 GMT

here is one example (with the data I created to give you the example above)

on the image image below, on the table, Data is sorted by the ranking (highest to lowest)

on the second image below, I choose DFHEY which has a rank=2

on this example, what is the previous rank value for you ? it is the rank of the first name on the listbox (on which the names are maybe sorted by alphabetical sorting, which not makes sense) or what ?

Re: How to show RANK in Text object

rkpatelqlikview — Wed, 14 Mar 2018 11:29:39 GMT

DFHEY which has a rank=2 because the value is 825 which is the second highest. If the same name DFHEY next day the value is increased to 1000 which is the top value then rank is 1. At this time our text box will show the number 1.

along with one more text box which should show the previous rank that is 2 of the DFHEY name.

Re: How to show RANK in Text object

YoussefBelloum — Wed, 14 Mar 2018 13:03:15 GMT

So you use another dimension which is Date field ?

and you select a Name and a Date ?

Re: How to show RANK in Text object

rkpatelqlikview — Thu, 15 Mar 2018 04:41:51 GMT

Hi Youssef,

Not actually. Name only is the selection. I am thinking that can we store the Previous Rank to particular variable and call that variable in to the Text object?

Re: How to show RANK in Text object

rkpatelqlikview — Mon, 23 Apr 2018 09:44:46 GMT

Hi Youssef,

Hope you are doing great!

Please help me on the below expression to display the Rank in the Text object.

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), [Carrier Name])

thanks in advance

Re: How to show RANK in Text object

YoussefBelloum — Mon, 23 Apr 2018 10:19:54 GMT

Hi,

everything is going well thank you

assuming all your calculations are correct iside your expression, try this:

=Aggr(

rank(

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

+(Round((COUNT(DISTINCT {<[Carrier Name]>}[DEVICE NO]) -COUNT({<[Carrier Name]>}[Device Number]))/COUNT(DISTINCT {<[Carrier Name]>}[DEVICE NO]) *100 )/4 ) +

(Round(SUM({<controlled={'Yes'},[Carrier Name]>}Quantity)/SUM({<controlled={'Yes'}+{'No'},[Carrier Name]>}Quantity)*100)/4)

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

Re: How to show RANK in Text object

rkpatelqlikview — Mon, 23 Apr 2018 11:21:39 GMT

Excellent Youssuef, many Thanks for your great support.

Re: How to show RANK in Text object

YoussefBelloum — Mon, 23 Apr 2018 11:57:11 GMT

You're welcome Kumar

Re: How to show RANK in Text object

rkpatelqlikview — Wed, 25 Apr 2018 14:22:18 GMT

HI Youssuef,

Am able to show the rank in Text object. Meanwhile i need to show Denominator out of no. Carriername.

Ex: There are 15 Carrier Names. Out of 15, the Carrier Rank is 5/15. How can i show this Denominator "15"

=Aggr(

rank(

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

+(Round((COUNT(DISTINCT {<[Carrier Name]>}[DEVICE NO]) -COUNT({<[Carrier Name]>}[Device Number]))/COUNT(DISTINCT {<[Carrier Name]>}[DEVICE NO]) *100 )/4 ) +

(Round(SUM({<controlled={'Yes'},[Carrier Name]>}Quantity)/SUM({<controlled={'Yes'}+{'No'},[Carrier Name]>}Quantity)*100)/4)

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

Thanks for your support

Re: How to show RANK in Text object

YoussefBelloum — Wed, 25 Apr 2018 14:54:04 GMT

Hi,

try this:

=Aggr(

rank(

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

+(Round((COUNT(DISTINCT {<[Carrier Name]>}[DEVICE NO]) -COUNT({<[Carrier Name]>}[Device Number]))/COUNT(DISTINCT {<[Carrier Name]>}[DEVICE NO]) *100 )/4 ) +

(Round(SUM({<controlled={'Yes'},[Carrier Name]>}Quantity)/SUM({<controlled={'Yes'}+{'No'},[Carrier Name]>}Quantity)*100)/4)

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

Re: How to show RANK in Text object

rkpatelqlikview — Thu, 26 Apr 2018 05:33:56 GMT

Thanks Youssef,

Its working overall. But here i have 5 groups. If i select any CarrierName it should give the rank and outof how many carriers in that Group. It should compare with in the group. I used below one. Please correct it. Nominator Group Rank is coming but Denominator is not working.

=Aggr(

rank(

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

+(Round((COUNT(DISTINCT {<[Carrier Name]>}[DEVICE NO]) -COUNT({<[Carrier Name]>}[Device Number]))/COUNT(DISTINCT {<[Carrier Name]>}[DEVICE NO]) *100 )/4 ) +

(Round(SUM({<[Control_Fleet (Y/N)]={'Yes'},[Carrier Name]>}Quantity)/SUM({<[Control_Fleet (Y/N)]={'Yes'}+{'No'},[Carrier Name]>}Quantity)*100)/4)

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

Re: How to show RANK in Text object

rkpatelqlikview — Thu, 26 Apr 2018 11:50:32 GMT

Thanks Youssef, Got it.

Re: How to show RANK in Text object

YoussefBelloum — Thu, 26 Apr 2018 11:57:38 GMT

Sorry Kumar, I was going back to you to get more details..happy to see that is it working for you