topic Count Window in QlikView

Count Window

Wed, 10 Oct 2012 15:01:51 GMT

Hello Every one,

I have following question

Expression sum by period each day.
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

Wed, 10 Oct 2012 17:56:57 GMT

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

Thu, 11 Oct 2012 18:50:25 GMT

Any Help please?

Re: Count Window

Mon, 15 Oct 2012 16:48:34 GMT

Hi All,

any help please?

Re: Count Window

Jason_Michaelides — Mon, 15 Oct 2012 17:51:23 GMT

Try this:

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

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

>} RPT_AMT)<0,

,0 ),0,vLast)

)

Where vLast is:

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

Hope this helps,

Jason