4 Replies Latest reply: Nov 4, 2012 4:30 PM by steve peroni RSS

    null values

    steve peroni


      is it possible to deal null values ?

      I've the following pivot


      Customer   Sales_CurrentYear     PreviousYear     Difference

      A                     1000                         800               200    

      B                         -                           500               -


      For customer B, I've a null value in current year and I can't calculate the difference.

      Is there a function to manage this problem ?


      Thanks in advance

        • Re: null values
          Stefan Wühl

          How do you want to define the difference, if Sales_CurrentYear is unknown? Set the sales to zero?

          IMHO, returning NULL in case of a unknown value is ok.


          What expressions are you using?

            • Re: null values
              steve peroni

              I would compare the sales performance between the current year and year-1.

              In the table above,   the sales amount for Customer B in the current year is zero, it means a bad performance:

              as value is -500  and in percentage we can consider -100%. How can I manage this null values ?

                • Re: null values
                  Stefan Wühl

                  You are using three expressions in your pivot chart, right? How do they look like?


                  edit: if you are not already doing so, use an aggregation function like sum in your sales expressions.



                    • Re: null values
                      steve peroni

                      Concerning total sales:


                      sum({<MY_YEAR={$(=Only(MY_YEAR))}, MY_MONTH= {"=$(=only(SALES_MONTH))"}>} GAINED_AMOUNT    )


                      Concerning percentage

                      = IF (ISNULL(sum({<MY_YEAR={$(=Only(MY_YEAR))}, MY_MONTH= {"=$(=only(SALES_MONTH))"}>} GAINED_AMOUNT    ) /sum({<MY_YEAR={$(=Only(MY_YEAR))}, MY_MONTH= {"<=$(=only(SALES_MONTH))"}>} VALOREACQM    )),0,sum({<MY_YEAR={$(=Only(MY_YEAR))}, MY_MONTH= {"=$(=only(SALES_MONTH))"}>} GAINED_AMOUNT    )/sum({<MY_YEAR={$(=Only(MY_YEAR))}, MY_MONTH= {"<=$(=only(SALES_MONTH))"}>} VALOREACQM    ))


                      In my mind, I 'm checking if the sum(.values for customer...)/ sum(...total sales) is null; if yes I put 0, else I do the calculathion.  But is there a better way to manage this issue ?

                      In order to make more simple the expression, is it possible to use the label (for example Total_sale)  instead of the explicit calculation (sum (.....total sales....) ?

                      Having an application with three languages (for example English, German and Dutch) may I use a calculated label ?