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

    Set analysis using getfieldselections() result

    André Ficken

      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

          What if you do this:

           

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

            • Re: Set analysis using getfieldselections() result
              Sunny Talwar

              Or this:

               

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

                • Re: Set analysis using getfieldselections() result
                  André Ficken

                  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
                      André Ficken

                      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.