topic Re: Need help for using Rank in QlikView

Need help for using Rank

Charlie007 — Sat, 16 Nov 2024 18:59:43 GMT

Can anyone tell me what's wrong with the following coding:

=Num(COUNT(DISTINCT{<CONTRACT_NUMBER= {"=Rank(COUNT(DISTINCT PO.PROC_AGENCY={'Supply Logistics APS'}),4)<=vTopN"}>}PO.PROC_AGENCY),'#,##0')

Actually, I want to use 02 conditions in this formula

1. Having procurement agency "Supply Logistics APS"

2. Apply to Top N records i.e.; Top 5, Top 10 or All Data

Please help me to correct this formula.

Thanks

Re: Need help for using Rank

tresB — Thu, 05 Mar 2020 06:58:04 GMT

One quickly spotted syntax issue.

=Num(COUNT(DISTINCT{<CONTRACT_NUMBER= {"=Rank(COUNT(DISTINCT {<PO.PROC_AGENCY={'Supply Logistics APS'}>}),4)<=vTopN"}>}PO.PROC_AGENCY),'#,##0')

If this doesn't resolve the issue, try to share a sample app explaining expected output in that context.

Re: Need help for using Rank

Charlie007 — Thu, 05 Mar 2020 07:09:55 GMT

My code is working fine, if I do not add the PO.Proc_agency condition, but in the formula I have to count number of contracts related with APS or ANS besides Top N records, so it means that now I have to cater 02 conditions in this formula.

I have changed my code as provided by you but nothing new happened as the result is 0 which is incorrect.

Re: Need help for using Rank

tresB — Thu, 05 Mar 2020 07:28:52 GMT

I am very bad in helping without sample app,specially when it is non-generic problem. Try and see if you could share a sample app or at least explain with a sample data set.

Re: Need help for using Rank

Brett_Bleess — Thu, 02 Apr 2020 12:07:47 GMT

The following may be of some help, not sure:

https://community.qlik.com/t5/Qlik-Design-Blog/Recipe-for-a-Pareto-Analysis/ba-p/1468497

https://community.qlik.com/t5/Qlik-Design-Blog/Recipe-for-a-Pareto-Analysis-Revisited/ba-p/1473684

https://community.qlik.com/t5/Qlik-Design-Blog/Rank-Function/ba-p/1466430

Sorry I do not have anything better, but hopefully one of these may get you what you need.

Regards,
Brett

Re: Need help for using Rank

Charlie007 — Tue, 14 Apr 2020 08:00:53 GMT

I hope that the following information will help you understand my issue.

The expression used against each 5 columns is given below:

EXPRESSION BEHIND 1ST COLUMN
=Count(DISTINCT{<PO.PROC_AGENCY={'Supply Logistics
APS'}>} PO_NUMBER)

EXPRESSION BEHIND 2ND COLUMN
=Count(DISTINCT{<PO.PROC_AGENCY={'Supply Logistics
ANS'}>} PO_NUMBER)

EXPRESSION BEHIND 3RD COLUMN
=Num(Sum(TOTAL{<PO.PROC_AGENCY={'Supply Logistics APS'}
>} PO.PO_AMOUNT+PO.GST_AMOUNT),'#,##0')

EXPRESSION BEHIND 4TH COLUMN
=Num(Sum(TOTAL{<PO.PROC_AGENCY={'Supply Logistics ANS'}
>} PO.PO_AMOUNT+PO.GST_AMOUNT),'#,##0')

EXPRESSION BEHIND 5TH COLUMN
=Num(SUM({<PO_NUMBER= {"=Rank(SUM(PO.PO_AMOUNT
+PO.GST_AMOUNT),4)<=vTopN"}>}PO.PO_AMOUNT
+PO.GST_AMOUNT),'#,##0')

Re: Need help for using Rank

Charlie007 — Tue, 14 Apr 2020 08:03:43 GMT

I hope that the following information will help you understand my issue.

The expression used against each 5 columns is given below:

EXPRESSION BEHIND 1ST COLUMN =Count(DISTINCT{<PO.PROC_AGENCY={'Supply Logistics APS'}>} PO_NUMBER) EXPRESSION BEHIND 2ND COLUMN =Count(DISTINCT{<PO.PROC_AGENCY={'Supply Logistics ANS'}>} PO_NUMBER) EXPRESSION BEHIND 3RD COLUMN =Num(Sum(TOTAL{<PO.PROC_AGENCY={'Supply Logistics APS'} >} PO.PO_AMOUNT+PO.GST_AMOUNT),'#,##0')

EXPRESSION BEHIND 4TH COLUMN =Num(Sum(TOTAL{<PO.PROC_AGENCY={'Supply Logistics ANS'} >} PO.PO_AMOUNT+PO.GST_AMOUNT),'#,##0')

EXPRESSION BEHIND 5TH COLUMN =Num(SUM({<PO_NUMBER= {"=Rank(SUM(PO.PO_AMOUNT +PO.GST_AMOUNT),4)<=vTopN"}>}PO.PO_AMOUNT +PO.GST_AMOUNT),'#,##0')