8 Replies Latest reply: Nov 13, 2012 7:43 AM by Göran Hofstedt RSS

    problem with sum in nested expression

    Göran Hofstedt

      Hi

      Im having a problem with the sum in a expression. My goal is to sum the total value but to do that I have to add up diffrent set expression. I think it´s in this step the error occur. Maybe the expression should be written in another way, then please explain how this can be done, thanks.

       

      Attache a qvd. Please set year to 2012 and month to nov and INSATS to 7. I also have include some textfield in the qvd to try to explain the problem

       

      Edit: The expression simplyfied are as follow

       

       


      =((

      sum({<[Kvantitet Enhet] = {'Vecka'}, VECKOPER = {'2'}>}
      Kvantitet_Minuter)* 4.33 /30.31 /2 *(count(DISTINCT Datum))

      +

      sum({<[Kvantitet Enhet] = {'Månad'}, VECKOPER = {'0'}>}
      Kvantitet_Minuter/30.31) *count(DISTINCT Datum

      )))

      /60

       

      And with the data in the qvd it should be something as this - surly wrong with the ()

       

       

      =

      sum(80 * 4.33) /30.31 /2 *30

      +

      sum((80 /30.31) *30))

       

      /60

       

       

       

        • Re: problem with sum in nested expression
          Robert Hutchings

          Why do you need both sum and count. Do you want to count distinct datum or sum datum.

           

          I don't think (but maybe Im wrong) you can use both sum and count in one expression like this refer manual start of 20.4

           

          If I need to use both then I sometimes use aggr.

           

          say change

          count(DISTINCT Datum

           

          to something like  aggr (count(distinct Datum),by fields you want to count by))

           

          or you could obtain the appropriate count total by using count in script grouped by as appropriate

           


           


            • Re: problem with sum in nested expression
              Göran Hofstedt

              first. in the last exampel I was putting 30 insted of  15 in the second part, correct should be

               

              sum(80 * 4.33) /30.31 /2 *30

              +

              sum((80 /30.31) * 15))

               

              /60

               

              The aim of the count(datum)  ie count(date) is to multiply the sum value with number of days in selected period.

               

              The value "Kvantitet_Minuter" ie time in min can be set in three diffrent units, min per day, min per week or min per month. I have to recalculate that value to min per singel day and then multiply it with number of days selected.

              The FROM and TO period can also be part of a month so for thoose cases I also need to convert to time per day and the multiply with number of days for that period, even if the user select a hole month the period can be ie 15 days. And if the value are 100 min per month I need to convert it to days and then multiply with 15.

               

              Should I explain in a better way or is´t understandable?

                • Re: problem with sum in nested expression
                  Göran Hofstedt

                    I tryed

                  =
                  ((

                  sum({<[Kvantitet Enhet] = {'Månad'}, VECKOPER = {'0'}>} Kvantitet_Minuter/30.31) *aggr (count(distinct Datum),BESTNR))

                  +

                  (
                  sum({<[Kvantitet Enhet] = {'Vecka'}, VECKOPER = {'2'}>} Kvantitet_Minuter)* 4.33 /30.31 /2 *aggr (count(distinct Datum),BESTNR

                  )))

                  /60

                   

                  It gives me right result for each BESTNR, but no total sum

                  If I change BESTNR to antoher dimension it stills gives me wrong total sum.

                  • Re: problem with sum in nested expression
                    Robert Hutchings

                    Hopefully someone else can help you out (Im still learning and have no time at present)

                     

                    But when I have done similar I have either used

                     

                    -aggr (to get the appropriate count total) or

                     

                    -calculated the appropriate count total in script first

                     

                    But this may not work for you

                      • Re: problem with sum in nested expression
                        Göran Hofstedt

                        When changed the aggr to 1 it gives right result so my problem seems to be in that part. Thanks for the help. Hopefully someone else can help me for here on.

                         

                        How can I replace * (1) with *count(date) in the following expression so the total sum works?

                         

                        =
                        (

                        (sum({<[Kvantitet Enhet] = {'Månad'}, VECKOPER = {'0'}>} Kvantitet_Minuter/30.31) * (1))
                        +
                        (
                        sum({<[Kvantitet Enhet] = {'Vecka'}, VECKOPER = {'2'}>} Kvantitet_Minuter)* 4.33 /30.31 /2 * (1))

                        )
                        /60

                         

                          • Re: problem with sum in nested expression
                            Robert Hutchings

                            Strange

                             

                            If

                             

                            aggr (count(distinct Datum),BESTNR)

                             

                            gives the right result and

                             

                             

                            =((sum({<[Kvantitet Enhet] = {'Månad'}, VECKOPER = {'0'}>} Kvantitet_Minuter/30.31) * (1))

                             

                            does too based on 1

                             

                            then when you replace the last 1 with aggr (count(distinct Datum),BESTNR) it should work I think

                             

                            hopefully someone can give you a working solution as I would like to know what's wrong

                             

                            You could calculate the date count in script

                              • Re: problem with sum in nested expression
                                Göran Hofstedt

                                Hi

                                Thanks for the help with the aggr function

                                I had done wrong with the ().

                                the correct expression are as

                                (sum(aggr(sum({<[Kvantitet Enhet] = {'Månad'}, VECKOPER = {'0'}>} Kvantitet_Minuter/30.31), BESTNR) * aggr(count(DISTINCT Datum), BESTNR)
                                )/60
                                )
                                +
                                (
                                sum(aggr(sum({<[Kvantitet Enhet] = {'Vecka'}, VECKOPER = {'2'}>}Kvantitet_Minuter * 4.33 /30.31 /2), BESTNR) * aggr(count(DISTINCT Datum), BESTNR)
                                )/60)