How do you create a straight table filtered by top 5 results of another straight table

[#1]

Hi everybody,

I'm new to QlikView.  I've been searching around trying to find a solution to my question.

For simplicity, I have 4 straight tables.  Let's say table 1 has a dimension, Invoice Type, and a sum expression for the total.  Is there a way to have table 2-4 be updated based on the results of table 1?  The data for table 2-4 is dynamic and dependent on the results of table 1 since the results of table 1 can always change.  I've included a sample of table 1 and table 2, but table 3 and 4 will be similar except table 3 data will be based on the #2 invoice type from table 1, and table 4 data will be based on the #3 invoice type from table 1.  All of the table names will change based on the invoice type which changes based on their totals for the period being analyzed.

Any help with a solution will be greatly appreciated.  A sample .qvw would be even more greatly appreciated.

Table 1 - Invoice Type Summary

Invoice Type	Total
Invoice Type 1	$100K
Invoice Type 2	$90K
Invoice Type 3	$80K
Invoice Type 4	$70K
Invoice Type 5	$60K

Table 2 - Invoice Type 1 Detail (Dynamic Title)

• Dimension filtered based on the #1 item invoice type from table 1
• Shows the top 3 results, by customer, of the specific invoice type

Customer Name	Amount
Customer Name 1	$50K
Customer Name 2	$10K
Customer Name 3	$5K

[Anil_Babu_Samineni]

What are you expecting?

[fkeuroglian]

Hi Vic

Would be easier if you attached and example, to help us to understand what you want

Fernando K.

[sunny_talwar · Accepted solution]

This?

[#5]

Hi,

My apologies.  My expectation was the tables that I included in my original post.  I should've been more clear.  Sunny T provided a solution.

[#6]

Hi,

Sorry that I didn't attach a sample file for reference.  I think Sunny T was able to answer the question though.

[#7]

Thank you so much for this.  For table 2, 3, and 4, is there any way to make it so it doesn't show the sum of the customer, but rather the individual invoice/customer combo with the highest amount?  For example, based on the xlsx attached to your post, for table 2, the top 3 customers that fall within invoice type 6 would be customer 7 $9,994.79, customer 6 $9,981.91, and customer 7, $9,974.40.  The same logic would apply for table 3 and 4.

[sunny_talwar]

Try these expressions:

If(Rank(Max({<[Invoice Type] = {"=Rank(Sum(Value), 4, 1) < 2"}>}Value), 4, 1) <= 3, Max({<[Invoice Type] = {"=Rank(Sum(Value), 4, 1) < 2"}>}Value))

If(Rank(Max({<[Invoice Type] = {"=Rank(Sum(Value), 4, 1) < 3 and Rank(Sum(Value), 4, 1) > 1"}>}Value), 4, 1) <= 3,

  Max({<[Invoice Type] = {"=Rank(Sum(Value), 4, 1) < 3 and Rank(Sum(Value), 4, 1) > 1"}>}Value))

If(Rank(Max({<[Invoice Type] = {"=Rank(Sum(Value), 4, 1) < 4 and Rank(Sum(Value), 4, 1) > 2"}>}Value), 4, 1) <= 3,

  Max({<[Invoice Type] = {"=Rank(Sum(Value), 4, 1) < 4 and Rank(Sum(Value), 4, 1) > 2"}>}Value))

[#9]

Thank you so much for this.  Your expression worked.  For some reason though I was having trouble sorting by the expression, so I ended up adding the Value dimension in addition to the expression, then sorting it by the dimension and hiding that column in the presentation.  I also had to disable the interactive sorts.  Although this wasn't the ideal solution for sorting, however it worked.  I'll have to figure out why I'm unable to interactively sort against the expression.

[sunny_talwar]

Not sure what the sorting issue that you have ran into? Is this something you need help with, or is this something you can live with?