6 Replies Latest reply: Aug 7, 2012 1:54 PM by Stefan Wühl

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?

• Re: Set analysis for one field greater than another

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

>} [Sales])

• Re: Set analysis for one field greater than another

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

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

For example, use

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

• Re: Set analysis for one field greater than another

can you post a sample application?

• Re: Set analysis for one field greater than another

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

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)