topic Re: Condition to filtrate data in QlikView

Condition to filtrate data

Wed, 17 Jun 2015 12:27:09 GMT

Hi all,

I'm new to QW and have a straight table that sums the the cost of of all products over time. My question is if i somehow can enter a condition that lets me filtrate out all data for the sum(cost) expression below or above a sertain value, for example to only show all products that have a value of more than 300.000 and less than -300.000 in the straight table and what that expression would look like?

Best regards!

Re: Condition to filtrate data

sunny_talwar — Wed, 17 Jun 2015 12:29:21 GMT

Try this:

If(fabs(Sum(Cost)) > 300.000, Sum(Cost))

Re: Condition to filtrate data

avinashelite — Wed, 17 Jun 2015 12:30:22 GMT

hi try like this:

if(sum(cost)>300 or sum(cost)<-300, sum(cost))

Re: Condition to filtrate data

Wed, 17 Jun 2015 12:45:20 GMT

hmm, cant get it to work, the deffinition looks like this:

({<Year={$(vYear4)},Quarter={$(vQuarter2)}>} Cost) - Sum({<Year={$(vYear3)},Quarter={$(vQuarter1)}>} Cost)

It calcs the variance between different quarters and i only want the <300.000 and <-300.000 values to show.

Re: Condition to filtrate data

avinashelite — Wed, 17 Jun 2015 12:53:01 GMT

Hi Hampus,

apply the condition on the whole set of expression:

if(sum({<Year={$(vYear4)},Quarter={$(vQuarter2)}>} Cost) - Sum({<Year={$(vYear3)},Quarter={$(vQuarter1)}>} Cost)>300 or

sum({<Year={$(vYear4)},Quarter={$(vQuarter2)}>} Cost) - Sum({<Year={$(vYear3)},Quarter={$(vQuarter1)}>} Cost)<-300,sum({<Year={$(vYear4)},Quarter={$(vQuarter2)}>} Cost) - Sum({<Year={$(vYear3)},Quarter={$(vQuarter1)}>} Cost))

Can you please share your app

Re: Condition to filtrate data

sunny_talwar — Wed, 17 Jun 2015 12:53:29 GMT

Try this:

If(fabs(Sum({<Year={$(vYear4)},Quarter={$(vQuarter2)}>} Cost) - Sum({<Year={$(vYear3)},Quarter={$(vQuarter1)}>} Cost)) > 300.000,

Sum({<Year={$(vYear4)},Quarter={$(vQuarter2)}>} Cost) - Sum({<Year={$(vYear3)},Quarter={$(vQuarter1)}>} Cost))

Best,

Sunny

Re: Condition to filtrate data

Wed, 17 Jun 2015 13:32:51 GMT

Think i'm doing something wrong, it wont sort out the above or below values. Can't share the original files but made a mini-version of the original with very limited data but it shold roughly show what i wanna do. The values in this data dosen't come close to 300000 as a limit but anyways:P

Re: Condition to filtrate data

avinashelite — Wed, 17 Jun 2015 13:44:39 GMT

hope this is what your looking for