topic Re: Set analysis using getfieldselections() result in App Development

Set analysis using getfieldselections() result

andre_ficken — Tue, 24 Nov 2015 23:50:19 GMT

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

sunny_talwar — Wed, 25 Nov 2015 00:39:34 GMT

What if you do this:

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

Re: Set analysis using getfieldselections() result

sunny_talwar — Wed, 25 Nov 2015 01:23:01 GMT

Or this:

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

Re: Set analysis using getfieldselections() result

andre_ficken — Thu, 26 Nov 2015 16:06:52 GMT

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??

Thanks in advance again for your help!!

Re: Set analysis using getfieldselections() result

andre_ficken — Thu, 26 Nov 2015 21:14:07 GMT

Hi Sunny, After a lot of trying and failure I finally managed to get the function working.

Can I ask you 1 one thing related to this??

In my pivot table I have salesrep, customer and turnover of the selected year when the customer does not have any turnover in previous years. if there is no revenue the turnover column is null (shows '-').

How can I reduce the pivot to only show me the new customers?? I have tried to re-use the formula

of the expression. Since the outcome is the same, it should just display another column. The expression is ok, but clicking ok after editing the dimension it displays the message 'Error in calculated dimension' where I expect the other column.