7 Replies Latest reply: Aug 9, 2013 11:48 AM by lailagrasso RSS

    Count all positive values

      Dear all,

       

      I have a problem which I can't solve: I have a set of instruments for which I calculate the performance over a period of time. In a next step, I would like to count all instruments withh a positive performance. When I enter the following formula:

       

      count(distinct(if(exp(sum({<Date= {">=$(=StartDate)<=$(=EndDate)"}>} total Aggr(log(sum(EoP_PX)/sum(BoP_PX)),Date)))-1<0,[Instr ID]))) 

       

      it doesn't limit on positive values only but gives me the overall number of instruments. I can't figure out what is wrong here. Can someone please help?

       

      Thank you very much!

      Laila

        • Re: Count all positive values
          Azam Mullick

          Hi,

           

          The expression is a bit complicated for me to try and reproduce, but just breaking it down it reads:

           

          Count distinct [Instr ID] if (complicated sum minus 1) is less than 0.

           

          Removing the (complicated sum) expression and just plugging in some positive and negative values did return a result; however as the if statement is testing against less than zero right at the end, it is just counts those [Instr ID] with -ve values.

           

          Does that help? If I've misunderstood, then perhaps you can provide some results for:

           

          exp(sum({<Date= {">=$(=StartDate)<=$(=EndDate)"}>} total Aggr(log(sum(EoP_PX)/sum(BoP_PX)),Date)))

           

          Thanks,

          Azam

            • Re: Count all positive values

              hi Azam,

               

              thank you very much for the reply.

               

              You understand completely right. What the formula does is that it calculates the performance of instruments between a start and end date (which can be set by the user), by adding up the logarithmic daily performances up to that day with each other.

               

              I have the impression that the 'total aggr' might be the problem, but I just can't figure it out.

               

              kind regards,

              Laila

                • Re: Count all positive values
                  Yaseen Pullissery

                  Hello

                   

                  Are StartDate and EndDate variables or fields?

                   

                  If they are variables :

                  count(distinct(if(exp(sum({<Date= {">=$(StartDate)<=$(EndDate)"}>} total Aggr(log(sum(EoP_PX)/sum(BoP_PX)),Date)))-1<0,[Instr ID]))) 

                   

                  Else if they are fields try adding min and max functions to the StartDate and EndDate.

                  count(distinct(if(exp(sum({<Date= {">=$(=min(StartDate))<=$(=max(EndDate))"}>} total Aggr(log(sum(EoP_PX)/sum(BoP_PX)),Date)))-1<0,[Instr ID]))) 

                   

                  It will be helpful if you could attach the app with sample data.

                   

                  I hope this helps!

                   

                  Regards

                  MultiView

              • Re: Count all positive values
                Azam Mullick

                Hi,

                 

                I've had to simplify things a bit, but I think the text box has the expression that you need (although you need to re-do the set analysis for dates and add your log expression etc).

                 

                I think the main thing is that you need to aggregate over 2 dimensions: the date and the instrument ID.

                 

                Let me know how you get on,

                Azam

                 

                Message was edited by: a.mullick