topic Re: Set Analysis - Filter Expression by Expression in QlikView

Set Analysis - Filter Expression by Expression

Tue, 03 Dec 2013 18:56:59 GMT

Hello,

I have an value in my application, [ExtNetDollars], and I have an Input Box which exposes two varaibles; vMinNetSales and vMaxNetSales. I have a straight table which is showing [ExtNetDollars], and I want to set it up to filter any rows where [ExtNetDollars] is not within the range defined by these two variables. I tried to start by just limiting to rows where [ExtNetDollars] was greater than the min, by trying the below, but it didn't work;

sum({<ExtNetDollars > $(vMinNetSales)>}ExtNetDollars)

Any help would be appreciated.

Thanks!

Chris

Re: Set Analysis - Filter Expression by Expression

jerem1234 — Tue, 03 Dec 2013 19:05:10 GMT

Try something like this:

sum({<ExtNetDollars = {'>$(vMinNetSales)<$(vMaxNetSales)'}>}ExtNetDollars)

Hope this helps!

Re: Set Analysis - Filter Expression by Expression

Tue, 03 Dec 2013 19:17:41 GMT

Thanks!

This worked, but perhaps I didn't know what I really wanted to achieve. Here is an example of what my straight table looks like with a simple expression to show ExtNetDollars, no variables and no set analysis.

I then have the following values set for my two variables;

vMinNetSales - $0
vMaxNetSales - $51,000,000

What I then see is the following;

What I expected to happen was each district below or above $51,000,000 would dissappear, but it appears what QV is doing is evaluating the net sales at a lower level. How could I get the desired result?

Thanks!

Chris

Re: Set Analysis - Filter Expression by Expression

jerem1234 — Tue, 03 Dec 2013 19:27:27 GMT

What you needed to do was to compare after you have done the sum already, you can use this if statement as expression:

if(sum(ExtNetDollars)>vMinNetSales and sum(ExtNetDollars)<vMaxNetSales, sum(ExtNetDollars))

or if you don't like using if statements, calculate the sum in the script first.

Hope this helps!

Re: Set Analysis - Filter Expression by Expression

Tue, 03 Dec 2013 19:46:07 GMT

Perfect!

Thank you very much.

Chris