Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.....
What if you do this:
Sum({1<[Turnover year] = {"(='<' & Min([Turnover year]))"} >} [Turnover amount])
What if you do this:
Sum({1<[Turnover year] = {"(='<' & Min([Turnover year]))"} >} [Turnover amount])
Or this:
Sum({1<[Turnover year] = {"$(='<' & Num(Left(GetFieldSelections(Year), 4)), '##')"}>} [Turnover amount])
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!!
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.