Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
racer25
Contributor III
Contributor III

Rank Top 10 for a month

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

1 Solution

Accepted Solutions
afroz_shaik
Contributor III
Contributor III

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

View solution in original post

10 Replies
Michiel_QV_Fan
Specialist
Specialist

Try this:

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

(if CIC_INCOME_REC_MONTH  is a date)

MarioCenteno
Creator III
Creator III

Try This

Dimension

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

afroz_shaik
Contributor III
Contributor III

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III

Does your expression give you Top 10 records?

racer25
Contributor III
Contributor III
Author

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
Contributor III
Contributor III

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
Contributor III
Contributor III
Author

Spot on thank you - really appreciate your time on this

racer25
Contributor III
Contributor III
Author

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.

Qv_2018-11-08_12-41-39.png

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