How to set up filters in straight data table?

[#1]

Dear All,

I’m trying to set up filters for a straight table. Partly because I’m interested in only specific transactions from the entire database and partly due to large data volume (I keep on getting “out of memory” messages). My table has standard columns (date, client, SKU, sales person, value, unit sales price, unit cost etc.). I’m trying to set up the following filters but I cannot figure out where exactly they should be placed in the table / chart set up:

• Data filter – last 7 days only (or this week only). I already have a flag created in script for that but just don’t know where it should be
• Conditional filter showing only transactions where unit sales price was lower than unit cost
• Filter showing only top 100 transactions in terms of value (one of the colums).

Your help would be much appreciated

Best regards

Thomas

[jpapador]

For the last seven days flag you will want to use it as set analysis in all of your expressions (ex: Sum{$<FlagField={1}>}DollarAmt))

For the second you could either create another flag field and use it in the set analysis or you could use a calculated dimension in place of the transaction dimension.  The calc dimension would be something like

If(Sales Price < Unit Price, Transaction Number, Null()) and make sure to check suppress null values

The last piece can be accomplished by dimension limits.  Ensure that value is your first dimension, then go into dimension limits and set it to show largest 100 values. ensure that you uncheck show others.

[Nicole-Smith]

1.  Set analysis that looks like {<YourFlagField={1}>} in your expression, so your expression would look something like sum({<YourFlagField={1}>} Sales)

2.  Possibly more set analysis that looks like {<Transaction={'=sum(UnitSalesPrice)<sum(UnitCost)'}>} in your expression, if in combination with (1), {<YourFlagField={1}, Transaction={'=sum(UnitSalesPrice)<sum(UnitCost)'}>}, so your expression would look something like sum({<YourFlagField={1}, Transaction={'=sum(UnitSalesPrice)<sum(UnitCost)'}>} Sales)

3.  Dimension Limits tab > set to Largest 100

[#4]

Thank you for your reply. For some reason I still get the message " out of memory". - given that the table should display only 100 transactions (out of 400k base). This may be due to the fact that I'm putting expressions in the wrong place.?

Where should  sum({<YourFlagField={1}, Transaction={'=sum(UnitSalesPrice)<sum(UnitCost)'}>} Sales) go?

Expressions\Conditional or Expressions\Definition?

Btw. what is the difference here between conditional and definition in expression field?

Any other reasons why I could be getting the message out of object memory?

Thanks again

[Peter_Cammaert]

Most often you get "out of memory"-messages because you're experimenting with cartesian products. Check whether your table shows columns that have no association whatsoever with each other (i.e. no link between the tables in your table viewer). If so, QlikView wil ltry to combine every value of the first field with every possible value of the second field.

Best,

Peter

[Nicole-Smith]

Definitely check what Peter Cammaert said.  There may be something wrong with your data model.

What I gave you should go into the Expressions\Definition section.  That is where the calculation is actually made.  Expressions\Conditional is if you want to use the calculation sometimes but not other times (so it dynamically shows and hides the calculation).

[#7]

Nicole,

I have managed to overcome the out of memory problem. However, formula don't seem to give the expected result

In the expression field i get the total value of each transaction but no filtering is done. I see the list of all transactions whether they meet the criteria or not. The goal that I want to achieve is to have visibility in the table only of transactions that fill in my criteria (so Selling price < unit price).

Also dimension limit doesn't seem to work as well. I set it on sales value (100 largest) but nothing happens - I still get the entire list..

Suggestions more than welcome

Thanks again

[Nicole-Smith]

Without being able to see your actual .qvw, I don't know if I can help you.  Please post a sample .qvw file: Preparing examples for Upload - Reduction and Data Scrambling

[Nicole-Smith]

You're using this formula, but you don't have any field named Transaction:

What Transaction field are you trying to filter on?  You need to replace Transaction in that formula with whatever your Transaction field is named.

[#10]

Nicole,

unfortunately doesn't work. Just checked that. Any other way to do that? Perhaps through some changes in script?

Regards,

T