topic Re: Creating a query (calculated field) to use as a selection criteria in QlikView

Creating a query (calculated field) to use as a selection criteria

Mon, 13 Jul 2015 13:01:14 GMT

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

Re: Creating a query (calculated field) to use as a selection criteria

alexandros17 — Mon, 13 Jul 2015 14:18:48 GMT

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

Re: Creating a query (calculated field) to use as a selection criteria

Josh_Good — Mon, 13 Jul 2015 16:27:37 GMT

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