Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)