Creating a query (calculated field) to use as a selection criteria
I am loading in my data in the following format;
Project ID Year Total Cost Total Benefit
1 14 £10,000 £5,000
1 15 £5,000 £0,000
1 16 £0 £1,000
2 14 £7,000 £2,000
2 15 £6,000 £1,000
3 15 £1,000 £0
I would like to filter my data for projects that deliver a benefit of more than £0. When I simply put an IF statement into my load script, it evaluates each piece of data getting loaded in. That means that red line above gets tagged with false... yet the other two lines (in green) of financials for Project ID = 1, get a true.
What I really want it to do, is evaulate the project as a whole, and show that Project ID 1 does have a benefit, and therefore when I make my filter on my dashboard, it gives me all the data for that project.
If it were a Access Database, I guess I would be looking to create a query, with a calulated field, in which I could then use to apply my filter on. I have no idea how to achieve this in Qlikview though.
Re: Creating a query (calculated field) to use as a selection criteria
I would recommend you perform this filter in the UI and not at the time of load the data. This will avoid limiting the analysis you can do on the data. You may want to consider creating a specific button to make the selection as desribed in this posting: Using Expressions to Make Selections
You also cloud do it in he expression in the chart using an expression in the dimension such as as If (Sum([Total Benefit]) > 0, [Project ID])