Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set analysis for one field greater than another

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?

6 Replies
giakoum
Partner - Master II
Partner - Master II

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

>} [Sales])

depending on your field names

Not applicable
Author

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

swuehl
MVP
MVP

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])

or

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

giakoum
Partner - Master II
Partner - Master II

can you post a sample application?

Not applicable
Author

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.

swuehl
MVP
MVP

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)