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

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.


Any help would be much appreciated.


Many thanks in advance

2 Replies
alexandros17
Partner - Champion III
Partner - Champion III

If the table containing your values is for example Tab1 then:

Load * resident Tab1;

left join

Load [Project ID], Sum(Total Benefit) as Prj_Tot resident Tab1 Group by [Project ID];

Now all the rows containing a value for Prj_Tot are the rows you need ...

Josh_Good
Employee
Employee

Hi Simon,

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

-Josh

Qlik