1 Reply Latest reply: Feb 25, 2012 11:39 AM by Stefan Wühl RSS

    only(Year)-1 in Expression

    Sravan Puppala

      =Sum({$<Year={$(=Only(Year))}>} {<TD_FactTable.KOSTEN_ERLOES = {'E'} >} distinct TD_FactTable.BETRAG)

      -

      Sum({$<Year={$(=Only(Year))}>} {<TD_FactTable.KOSTEN_ERLOES = {'K'} >} distinct TD_FactTable.BETRAG)

      The above formula works perfectly in a Pivottable Expression. The below with Only(Year)-1 gives the same value as above expression with Only(Year)

       

       

      =Sum({$<Year={$(=Only(Year)-1)}>} {<TD_FactTable.KOSTEN_ERLOES = {'E'} >} distinct TD_FactTable.BETRAG)

      -

      Sum({$<Year={$(=Only(Year)-1)}>} {<TD_FactTable.KOSTEN_ERLOES = {'K'} >} distinct TD_FactTable.BETRAG)

       

      Seems to be silly but I am not getting. May be some one can help.

       

      Thanks

      Sravan

        • Re: only(Year)-1 in Expression
          Stefan Wühl

          I am not sure if using two set expressions will work, like you do using two {$<...>}{<..>}

           

          I assume your first set expression is then just discarded, and you are not notice it if you select one year (because the first set expression will than just not change your set anyway).

           

          Try a set expression like

          {$<Year={$(=only(Year))}, TD_FactTable.KOSTEN_ERLOES = {E} >}

           

          resp.

          {$<Year={$(=only(Year)-1)}, TD_FactTable.KOSTEN_ERLOES = {E} >}

           

          Hope this helps,

          Stefan

           

          edit: same set identifier used, so removed a comment on that..