2 Replies Latest reply: Jan 19, 2014 11:13 PM by Veena Vijayan RSS

    Above function group by dimension

      Hi All

       

      I have a requirement of replacing nulls with previous value which I am able to achieve by above function.

      Is there any option to limit it only to date dimension and group by on other dimension.

       

      Thanks in Advance

      Veena

        • Re: Above function group by dimension

          Veena,

           

          In fact, you need to order your table by Other Dimensions and  Date

          And also, use the peek() or previous() functions, sth like:

          if (NOT isnull(Price), Sales, if(previous(Customer)=Customer AND previous(Product)=product, peek('Price')) ) as 'Price',

          [...]
          FROM [...]

          ORDER BY Customer, Product, Date;

          Here, I want to fetch the previous Price only if the Price is null AND  if it is the same Customer and the same Client as the previous one.

           

          Fabrice

            • Re: Above function group by dimension

              Hi Fabrice,

               

              I understand that peek function will help in this but it is a static value. We are having a cumulative measure and when displayed over multiple dimension in pivot table results in null values. So we are looking for a dynamic function with which we can achieve the same in expression. Above function is working fine.

              [Actual Sale]=IF(ISNULL(MAX(Sales)),Above(([Actual Sale]),1),MAX( Sales))

               

              But I need it group by other dimensions like region. One product sale value should not be carry forward to next region. Is there any way to achieve this.

               

              Thanks in Advance

              Veena