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

[Michiel_QV_Fan]

Try this:

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

(if CIC_INCOME_REC_MONTH  is a date)

[MarioCenteno]

Try This

Dimension

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

[afroz_shaik]

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)

[racer25]

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

[afroz_shaik]

Does your expression give you Top 10 records?

[racer25]

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

[afroz_shaik · Accepted solution]

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....

[racer25]

Spot on thank you - really appreciate your time on this

[racer25]

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