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


        • Re: Above function group by dimension



          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.



            • 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