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!


        • Re: Count all positive values
          Azam Mullick



          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)))




            • 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,


                • Re: Count all positive values
                  Yaseen Pullissery



                  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!




              • Re: Count all positive values
                Azam Mullick



                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,



                Message was edited by: a.mullick