Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Every one,
I have following question
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
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...
Any Help please?
Hi All,
any help please?
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