2 Replies Latest reply: Apr 4, 2013 3:13 AM by Óscar Iglesias RSS

    Problem with set analysis in pivot table

      Hello, I have a problem with an expression in a pivot table in wich I use set analysis.

      I have three dimensions (Empresa, Entidad and Fecha). Fecha (is a date) is pivoted like attached picture.

       

      Captura.PNG

       

      The expression content is:

       

      =IF(Fecha>=Min(Fecha) AND Fecha<=Max(Fecha),

      IF(ISNULL(SUM({$<Año=,Meses=,Día=,Fecha={'<=$(=Max(Fecha))'}>}SALDO)),0,SUM({$<Año=,Meses=,Día=,Trimestre=,Fecha={'<=$(=Max(Fecha))'}>} SALDO))

      ,0)

       

      What I want to get is the cumulative amount from the beginning of time for each date but I think that the set analysis of the dimension Fecha don't works. I only get the amount for each Fecha column. The same expression is in other table (not pivot table) and works fine.

       

      Any ideas?

       

      Best regards,

       

      Óscar Iglesias.

        • Re: Problem with set analysis in pivot table
          Kaushik Solanki

          Hi,

           

               If your purpose is to have cumulative data for date dimension then try this expression.

           

               Rangesum(before(Amount),Sum(SALDO))

           

               Lable this expression as Amount.

           

          Regards,

          Kaushik Solanki

            • Re: Problem with set analysis in pivot table

              Hi Kaushik, thanks for your reply.

               

              The solution you propose is a good approximation but does not solve my problem because I'm trying to do what you propose in the first column but add all previous columns that are not displayed on the screen. So I used my expression analysis set, so that the Fecha dimension is filtered differently.

               

              What I mean is, if in my example appear 05/11/2012 until 09/11/2012, the calculation for 05/11/2012 is the value for the same day by adding the values do not appear in the chart to 05/11/2012 inclusive. For the 06/11/2012 would be the same, all values until 06/11/2012, and so with all the dates shown.

               

              Regards,

               

              Oscar Iglesias