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

    set analysis expression

      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 de Arce



          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

              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?



                • set analysis expression
                  Stefan Wühl

                  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.




                    • set analysis expression

                      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
                          Stefan Wühl

                          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
                    Goran Korsgren

                    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

                      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.