topic Re: set analysis for 2 intersecting fields where one is greater than 0 in QlikView

set analysis for 2 intersecting fields where one is greater than 0

Wed, 29 Oct 2014 18:27:17 GMT

Argh. I should know how to do this, but I haven't successfully figured this out yet. I'm trying to add a button to my qvw that specifies when you click it, the resulting table will give me the sum of revenue, but only for those contracts that have a positive revenue stream (greater than $0).

First step was creating the text box and assigning the action of "select in field". For the field, I specified the field name of Revenue which hopefully is correct. Then for the search string I can't seem to get the set analysis correct.

What I tried (with several variations) was the following (the extra brackets around revenue are being I actually have a longer field name which I've truncated here):

Sum({$<[Contract Number]=P({1<[Revenue]={">$(=0)"}>}[Contract Number])>}[Revenue])

My guess is I don't need the entire piece, since I'm not trying to actually "sum" the revenue - it's already a dimension that is imported and not an expression - but I can't seem to figure out where I've gone wrong, or if I'm even on the right track...

Re: set analysis for 2 intersecting fields where one is greater than 0

JonnyPoole — Wed, 29 Oct 2014 20:22:46 GMT

How about something like this ?

Search string is: ='>=0'

Re: set analysis for 2 intersecting fields where one is greater than 0

Wed, 29 Oct 2014 20:33:18 GMT

Thanks - I tried that originally and while it solves for revenue being greater than $0, it doesn't limit it at the contract level specifically.

I have a data island set up where the user can select what fields they want to include. They might choose a broader level, such as customer and if I don't put something in set analysis it would eliminate customers who overall had negative revenue even if some of their contracts produced positive revenue.

Likewise, another field in the data island is at the individual asset level. There can be individual swings in revenue generated by individual assets within a contract, but it's the contract level overall that we're concerned about making money.

Re: set analysis for 2 intersecting fields where one is greater than 0

JonnyPoole — Fri, 31 Oct 2014 18:27:24 GMT

Hi just getting back to this. So it sounds like instead of filtering the raw sales field, it better to filter the contract field based on the sales subtotal for that contract.

I don't have Contract in my sample data set but i do have region. If i want to select regions where the sum(Sales) for the region is greater than 0 , i can use this as the expression for my select in field action on the field Region:

='("' & concat( distinct if( aggr(sum(Sales),Region) > 0, Region) ,'"'& '|' & '"') & '")'

it creates a string like this which is valid for the action.

("Asia"|"Europe"|"Middle East"|"Prev. Soviet Rep.")