4 Replies Latest reply: Nov 26, 2015 4:14 PM by André Ficken

# Set analysis using getfieldselections() result

Hi there,

I am stuck on a set analyses definition using the result of a getfieldselections result... I am not sure whether this will actually work...

This is my set analyses in simple format: Sum( {1 <  [Turnover year] = {"<2014"} >} [Turnover amount])

This works fine.

There is a general selectionbox  on a Year column... selections can be, for example, a single year (eg. 2014)  or multiple years (eg. 2014 and 2015).

I would like to sum all turnover in years less than the first year in my getfieldselections() result.

When selecting 2 years, a left(getfieldselections([Year]), 4) will give me the first year.

So in short: How do I replace this: {"<2014"} in the set analyses by using the result of left(getfieldselections([Year]), 4)

I have been struggling with {"<\$(=left(getfieldselections([Year]), 4))"} but no luck on the expected result sofar...

any help to resolve this is gladly accepted.....

• ###### Re: Set analysis using getfieldselections() result

What if you do this:

Sum({1<[Turnover year] = {"(='<' & Min([Turnover year]))"} >} [Turnover amount])

• ###### Re: Set analysis using getfieldselections() result

Or this:

Sum({1<[Turnover year] = {"\$(='<' & Num(Left(GetFieldSelections(Year), 4)), '##')"}>} [Turnover amount])

• ###### Re: Set analysis using getfieldselections() result

Hi Sunny, I am more in favor of the last example using the getfieldselections().

I cannot get it to work though.

My base line that works correctly is:   Sum( {\$ <[Omzet jaar] = {"<2014"} > } [Omzet bedrag]))

The part that I need to change is: <[Omzet jaar] = {"<2014"} >

in the {"<2014"} part I need the year replaced by the value of left(GetFieldSelections([Jaar factuur]),4)

Omzet jaar = Revenu Year, Omzet bedrag = Revenu Amount, Jaar factuur = selection,

I am still struggling with it as you can see...

Finally after I get this to work, I would like the pivot table to only show new customers (no revenu

BEFORE getfieldselections() years, but EXISTING revenu IN getfieldselections() years. How can I keep new customers on this list and eliminate existing customers??