topic Re: Set analysis for one field greater than another in QlikView

Set analysis for one field greater than another

Tue, 07 Aug 2012 14:59:13 GMT

Hi community,

Lets say I have two fields that I would like to use in set analysis. One of them is YearField1 and the other is YearField2. How to I get the set of values where YearField1>=YearField2 and YearType1<=YearField3?

Re: Set analysis for one field greater than another

giakoum — Tue, 07 Aug 2012 15:03:45 GMT

sum({$<[YearField1]={">=$([YearField2])"}, [YearType1]={"<=$([YearField3])"}

>} [Sales])

depending on your field names

Re: Set analysis for one field greater than another

Tue, 07 Aug 2012 16:14:19 GMT

For some reason, that gives me the total of all selections regardless of the selection I make.

Re: Set analysis for one field greater than another

swuehl — Tue, 07 Aug 2012 16:20:20 GMT

I think you need to create a primary key since you want to select single records, right?

For example, use

LOAD

recno() as RecID,

...

in your script to create the Key.

then you can try something like

=sum({$<RecID = {"=YearField1>=YearField2"}*{"=YearType1<=Yearfield3"} >} [Sales])

=sum({$<RecID = {"=YearField1>=YearField2 and YearType1<=Yearfield3"} >} [Sales])

Re: Set analysis for one field greater than another

giakoum — Tue, 07 Aug 2012 16:28:56 GMT

can you post a sample application?

Re: Set analysis for one field greater than another

Tue, 07 Aug 2012 16:54:31 GMT

Basically I have 2 fields, termination date and employment date. I want a count of people that are employed at my company monthly so I want terminationdate>selectabledate and employmentdate<selectabledate. I tried doing an inline load for current date.

Re: Set analysis for one field greater than another

swuehl — Tue, 07 Aug 2012 17:54:43 GMT

So current date is your selectabledate and a data island from where you select one value?

Then try something like

=count(

terminationdate = {">=$(=only(CurrentDate))"},

employmentdate ={"<=$(=only(CurrentDate))"}

distinct employeenum)