4 Replies Latest reply: Oct 15, 2012 1:51 PM by Jason Michaelides RSS

    Count Window

      Hello Every one,

      I have following question

       

       

      1. Expression sum by period each day.
      2. after sum Count days with –ve amount and add it for a given days (asofperiod).

       

      Basically I am trying to find –ve amount trending for last 30 days, last 60 days for a selected asofperiod.

      I have Period dates created for each asofperiod ffor last_30/last_60 and last_90 days. It is like creating window of 30 days and find –ve amount count as one

      Please see attached sample file and result

       

      I tried following but it did not work

       

       

       

       

      //sum(//{<AsOfPeriod = {'>$(=date($(VMaxAsOFPeriod)-$(VDaysForTrending))) <=$(=date($(VMaxAsOFPeriod)))'}
      sum({<AsOfPeriod = {'>$(=date($(VMaxAsOFPeriod)-$(VDaysForTrending))) <=$(=date($(VMaxAsOFPeriod)))'}>}
          if(aggr(sum({<AsOfPeriod = {'>$(=date($(VMaxAsOFPeriod)-$(VDaysForTrending))) <=$(=date($(VMaxAsOFPeriod)))'},     
                        last={'last_30'}     
                     >} RPT_AMT),PERIOD )
              <0,1,0) )
      
      
      
      
      

       

      $(VDaysForTrending) gives be how many asofperiod(going back of selected date) want to show( Trending days)

      Thank you in advance
        • Re: Count Window

          Hi All,

          Please see expression below and attached result i am getting and i think it is correct. I am able to get the result but i have 30 hard coded, some haow i have to use PERIOD, which is last_30 / last_60, so basically I should be counting -ve amount for last 30 days for a selected asofperiod

           

          rangesum(above(if(sum({<AsOfPeriod = {'>$(=date($(VMaxAsOFPeriod)-$(VDaysForTrending))) <=$(=date($(VMaxAsOFPeriod)))'}                    
           // Last = {'last_30'}//,            
                 >} RPT_AMT)<0,
            1
            ,0 ),0,30)
            )
          

           

           

          Please help with hints...

          • Re: Count Window
            Jason Michaelides

            Try this:

             

             

            rangesum(above(if(sum({<AsOfPeriod = {'>$(=date($(VMaxAsOFPeriod)-$(VDaysForTrending))) <=$(=date($(VMaxAsOFPeriod)))'}                    

             // Last = {'last_30'}//,            

                   >} RPT_AMT)<0,

              1

              ,0 ),0,vLast)

              )

             

            Where vLast is:

             

            =Num#(Subfield(Only(Last),'_',2))

             

            Hope this helps,

             

            Jason