7 Replies Latest reply: Sep 2, 2011 6:55 AM by Jason Newman RSS

set analysis expression

Brian Murphy

Is there any reason why this expression will not work, keep getting a null value, but I know that both 'Value' and 'GP' are not null figures.

 

sum({$< Date_MonthYear = {">=$(#vDate12)<=$(#vDateNow)"} >} (GP/Value))

 

 

  • set analysis expression
    Miguel Angel Baeyens

    Brian,

     

    If the syntax in the condition in the Set analysis is not correct or it returns strange results, the result of the whole aggregation function may be null. Check that it works without the set analysis, and if it does, you will know that the set modifier or syntax is not correct.

     

    Hope that helps.

     

    Miguel Angel Baeyens

    BI Consultant

    Comex Grupo Ibérica

    • set analysis expression
      Brian Murphy

      Thanks for the reply Miguel. It still does not work when I take out the set analyis.

       

      sum(GP/Value) gives me a null value

       

      sum(GP) gives me all the GP values

       

      sum(Value) gives me all the 'Value' values

       

      Im at a loss as to what im doing wrong, any ideas?

       

      Thanks

      • set analysis expression
        swuehl

        Hi brianm10,

         

        do you have values for GP and Value for each record or either GP or Value in a record?

        sum(GP/Value) is calculating the division on record level, so latter will be a problem.

         

        Maybe you could try using two sums with set expression and devide the two sums? But it depends what you want to achieve, I think GP/Value may some kind of percentage, so you might not want to calculate the sum of percentages, but the percentage of summed values anyway.

         

        Regards,

        Stefan

        • set analysis expression
          Brian Murphy

          I dont have values for each record. Some records have both a 'Value' and 'GP' figure but there are some null values.

           

          I'm guessing this is why Im getting a null value? How can I overcome the issue?

          • set analysis expression
            swuehl

            I think even if you don't have values for each record, you should not get  NULL back from your sum function. Hm, don't know why you get NULL (I assume you get NULL if you say null and not zero, right?).

             

            But back to my last post, have you tried something like

            =sum(GP) / sum(Value)

             

            [maybe with your set expression in each sum]?

             

            This is not the same as sum(GP/Value), but I still assume that's probably what you want if (GP/Value) is some kind of share / percentage (like profit%).

  • set analysis expression
    GandalfGray

    Hi Brian

     

    If you leave the Label of the expression empty you should be able to see the evaluation of your set expression when you hover the mouse over the column heading, this is often a good trick if you are having problems with set expressions.

  • set analysis expression
    Jason Newman

    Just a thought.....

     

    I was having a very similar problem with calculating percentages. The solution to my problem was to un-select the Relative box for that column and the calculations worked.

     

    It is probably not the solution to your problem but it might help someone "googling" for an answer to their problem.

     

    Jason