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: 
Not applicable

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

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 TypeTotal
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 NameAmount
Customer Name 1$50K
Customer Name 2$10K
Customer Name 3

$5K

1 Solution

Accepted Solutions
sunny_talwar

11 Replies
Anil_Babu_Samineni

What are you expecting?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
fkeuroglian
Partner - Master
Partner - Master

Hi Vic

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

Fernando K.

sunny_talwar

This?

Capture.PNG

Not applicable
Author

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.

Not applicable
Author

Hi,

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

Not applicable
Author

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

Capture.PNG

Not applicable
Author

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?