top 10 file no and transaction date based on amount along with name in straight table

[#1]

Hi all,

I need help in my current assigment where i want to achieve result in a straight table like ....

I need top 10 applicant name based on their amount (disbursement/debt balance) along with file no and Date (on which date this amount was disbursed in this file no). Date field is must in staright table .

Date and file no is must..........

Please find out the qvw doccument attached for your understanding and reference..........

Its very urgent......

Thanx in Advance...

Regards,

Dushyant

[vgutkovsky]

I think it would be better to have this in a pivot table. You will have multiple File#s and Dates per eligible Applicant name, correct? Create a calculated dimension that looks similar to the following:

aggr(
 if(rank(total sum(disbursement))<=10,[Applicant Name])
 ,[Applicant Name]
)

Select to suppress this dimension when value is null. Then add 2 regular dimension below it for File# and Date. Add whatever expression you want. The result should be a list of your top-10 applicants and all the corresponding File and Date info associated with them.

Regards,

[#3]

Hi All,

PLEASE FIND OUT MY APPLICATION QVW ATTACHED FOR YOUR UNDERSTANDING AND REFRENCE..........

[vgutkovsky]

See attached solution.

Regards,

[#5]

Hi Vlad,

I need date of this transaction along with file no ........ can you apply same logic for date field tooo.......

Regards,

Dushyant

[vgutkovsky]

You care about the top-10 customers, not top-10 dates. So once you list your top-10 customers you can add any other dimensions you want the usual way. Just make sure you suppress the customer when value is null.

Regards,

[#7]

Hi Vlad,

Can you send me this qvw with date field in straight table and pivot table because i am not getting proper date on my selection when i m making a selection like year 2010 and month jun then all date against the top 10 file no or amount is same......... can you look ito this and send me that qvw doccument in attachment ..

waiting for your reply,

Thanx in advance.......

Regards,

Dushyant

[vgutkovsky]

Yes, that will happen because you only have 1 June 2010 date for those top-10 customers (June 6, 2010). In fact, you only have 1 date per DebtNo per Month every month. So assuming your data is correct, don't worry, you're doing everything right--that's how it works.

Regards,

[llauses243]

Hi Vlad,

1.- Even is pending how replace option relative of Straight in Pivot, please see attached: Relative Pivot vs Straight.png

2.- Also i observe than rank's first 10 customer in top 10 not matches with rank's first 10 customer in top 20, please see attached schema.png

If is exact my observation then everyone must be careful

very thanks, Vlad

Best regards for Oleg, other genius like you.

Luis.

[vgutkovsky]

Luis,

You're correct that relative values are not allowed in pivot tables. That's why I actually wrote out the expression and divided it by the TOTAL sum (to manually create the percentage).

Regarding the 10 - 12 numbers you're seeing (I'm assuming that's what you meant), see the Help file and search for the Rank() function: "If all ranks within the sharing group fall on the low side of the middle value of the entire ranking, all rows get the lowest rank within the sharing group." You can change this by setting the mode code.

Regards,