
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
sum({$<[YearField1]={">=$([YearField2])"}, [YearType1]={"<=$([YearField3])"}
>} [Sales])
depending on your field names

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
For some reason, that gives me the total of all selections regardless of the selection I make.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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])


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
can you post a sample application?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
