topic Re: Rank Top 10 for a month in QlikView

Rank Top 10 for a month

racer25 — Wed, 07 Nov 2018 14:18:58 GMT

Hi,

I just about understand the rank function but believe I need to also introduce SET analysis into the mix also.

I have a formula =sum(if(CIC_COMPARABLE = 'Plan', CIC_NETINCOME,0))

I also have a field CIC_INCOME_REC_MONTH

My goal is to simply show the Top 10 'Plan' for the Current Month - whatever month we are in.

Would any be able to help me on this please.

Thanks,

Rob

Re: Rank Top 10 for a month

Michiel_QV_Fan — Wed, 07 Nov 2018 14:23:50 GMT

Try this:

sum( {<CIC_COMPARABLE = 'Plan', CIC_INCOME_REC_MONTH = {"$(=Month(Today())"}>}CIC_NETINCOME)

(if CIC_INCOME_REC_MONTH is a date)

Re: Rank Top 10 for a month

MarioCenteno — Wed, 07 Nov 2018 14:31:45 GMT

Try This

Dimension

=If(Aggr(Rank(Sum(CIC_COMPARABLE)), [CIC_NETINCOME])<=10, [ IC_NETINCOME], 'Others')

Re: Rank Top 10 for a month

afroz_shaik — Wed, 07 Nov 2018 15:05:42 GMT

Hi Rob,

Top 10(Customers,products etc..) has to be based on some value which may be sales.

Try this:

Sum({<CIC_Comparable ={"Plan"},CIC_Income_rec_Month={"$(=Month(Today())"},Customer = {"=Rank(sum(Sales))<=10"}>} Sales)

Re: Rank Top 10 for a month

racer25 — Wed, 07 Nov 2018 15:40:02 GMT

Hi Afroz,

sum( {<CIC_COMPARABLE = {'Plan'} , CIC_INCOME_RECOGNITION_MONTH = {"$(=month(today()))"}>} CIC_NETINCOME).

Thanks for very prompt answer I had to enclose Plan with {} to make work.

Appreciate your prompt response.

Thanks,

Rob

Re: Rank Top 10 for a month

afroz_shaik — Wed, 07 Nov 2018 15:42:16 GMT

Does your expression give you Top 10 records?

Re: Rank Top 10 for a month

racer25 — Wed, 07 Nov 2018 16:27:20 GMT

Hi Afroz,

I put presentation to 10 lines.... not the best idea.

Below is trying tp put actual field names in based your working - no error's in formula but not no results.

Sum({<CIC_COMPARABLE = {"Plan"} , CIC_INCOME_RECOGNITION_MONTH = {"$(=Month(Today())"}, CIC_DEBTORCODE = {"=Rank(sum(CIC_NETINCOME))<=10"}>} CIC_NETINCOME)

Any thoughts

Thanks,

Rob

Re: Rank Top 10 for a month

afroz_shaik — Wed, 07 Nov 2018 16:59:10 GMT

Try this:

Sum({<CIC_COMPARABLE = {'Plan'} ,CIC_INCOME_RECOGNITION_MONTH = {"=Month(today())"}, CIC_DEBTORCODE = {"=Rank(sum(CIC_NETINCOME))<=10"}>} CIC_NETINCOME)

Make sure both month formats in source and output are same. output format will be like Jan, Nov....

Re: Rank Top 10 for a month

racer25 — Wed, 07 Nov 2018 17:01:10 GMT

Spot on thank you - really appreciate your time on this

Re: Rank Top 10 for a month

racer25 — Thu, 08 Nov 2018 12:49:19 GMT

Hi Afroz,

I thought it was working but it afroz.shaik‌ but the Rank piece appears not to be looking at just the month but rather the whole year.

Our new formula is supposed be the same as the Plan values

Plan is

sum( {<CIC_COMPARABLE = {'Plan'} , CIC_INCOME_RECOGNITION_MONTH = {"$(=month(today()))"}>} CIC_NETINCOME)

New formula is

Sum({<CIC_COMPARABLE = {'Plan'} , CIC_INCOME_RECOGNITION_MONTH = {"$(=month(today()))"}, CIC_DEBTORCODE = {"=Rank(sum(CIC_NETINCOME))<=10"}>} CIC_NETINCOME)

Does the Rank element need to also include month ? I did try and kept getting errors ( or blanks ) - I'd appreciate anything you can do on this.

Thanks for your time on this.

Rob

Re: Rank Top 10 for a month

afroz_shaik — Thu, 08 Nov 2018 16:54:26 GMT

Hi Rob,

Please find below solution.

My test load script.

Data:

LOAD *,

Date(Day,'MM/DD/YYYY') AS Date,

MonthName(Day) as CIC_INCOME_RECOGNITION_MONTH;

LOAD * INLINE [

Day, CIC_DEBTORCODE, CIC_NETINCOME, CIC_COMPARABLE

11/15/2018, 1, 100, Plan

11/16/2018, 2, 101, Plan

11/17/2018, 3, 102, Plan

11/18/2018, 4, 103, Plan

11/16/2018, 5, 104, Plan

11/17/2018, 6, 105, Plan

11/18/2018, 7, 106, Plan

11/15/2018, 8, 107, Design

11/16/2018, 9, 108, Design

11/17/2018, 10, 109, Design

11/18/2018, 11, 110, Design

11/16/2018, 12, 111, Plan

11/17/2018, 13, 112, Plan

11/18/2018, 14, 113, Plan

11/15/2018, 15, 114, Plan

11/16/2018, 16, 115, Plan

11/17/2018, 17, 116, Plan

09/17/2018, 18, 117, Plan

01/18/2018, 19, 118, Plan

10/02/2018, 20, 119, Plan

11/03/2018, 21, 120, Plan

11/04/2018, 22, 121, Plan

];

Set Analysis you need:

Sum({<[CIC_DEBTORCODE]={"=Rank(Sum({<CIC_COMPARABLE = {'Plan'} ,[CIC_INCOME_RECOGNITION_MONTH]={[>=$(=MonthStart(Today())) <=$(=MonthEnd(Today()))]}>}CIC_NETINCOME))<11"}>}CIC_NETINCOME)