Rank Top 10 for a month

[racer25]

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

[afroz_shaik]

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)