Previous year

[krishnakv83]

I have  text box expression which is  working with current year.i'm trying to calculate previous year value in the text box but value is  zero.

Used below formula for previous year.

{<[Year]={$(=Max([Year])-1)>}

Is there any method to calculate previous year other than above method.If yes , please provide your view to calculate previous year.

This is to be resolved today.Pls help

Regards,

Venkat

[Anil_Babu_Samineni]

Your method looks okay? What is thee issue

[krishnakv83]

If I hard coded to Year = 2017 the expression is showing value but if I use {<[Year]={$(=Max([Year])-1)>} then expression is showing as zero .Also verified this {$(=Max([Year])-1)>} showing 2017 in the text box.

= num(avg({<[Year]={$(=Max([Year])-1)>}  aggr(

if('$(vPerformance)'='Delivery',

if($(vAcceptableDaysDelayed) >= fabs( avg({<[Year]={$(=Max([Year])-1)>}  aggr( $(eDOT_DiffDel) , $(eAggrLevel1) )) ) ,1,0)

,

),[Purchasedoc], [Purchaseposition]))*100     ,'#.##0,00')

I don't have any clue on this.pls help

Regards,

Venkat

[Anil_Babu_Samineni]

What are you getting simply using Max(Year)-1 in text box?? And how does your Year values looks like?

[krishnakv83]

Also verified this =Max([Year])-1 showing 2017 in the text box.

[Anil_Babu_Samineni]

from provided expression if you make static 2017 worked. then i believe with this condition should work

= num(avg({<[Year]={'$(=Max(Year)-1)'>}  aggr(

if('$(vPerformance)'='Delivery',

if($(vAcceptableDaysDelayed) >= fabs( avg({<[Year]={'$(=Max(Year)-1)'>}  aggr( $(eDOT_DiffDel) , $(eAggrLevel1) )) ) ,1,0)

, ),[Purchasedoc], [Purchaseposition]))*100    ,'#.##0,00')

OR

= num(avg({<[Year]={'$(=AddYears(Max([Year]),-1)'>}  aggr(

if('$(vPerformance)'='Delivery',

if($(vAcceptableDaysDelayed) >= fabs( avg({<[Year]={'$(=AddYears(Max([Year]),-1)'} >}  aggr( $(eDOT_DiffDel) , $(eAggrLevel1) )) ) ,1,0)

, ),[Purchasedoc], [Purchaseposition]))*100    ,'#.##0,00')

And, Finally what i understand is you missed one closed flower bracket?

= num(avg({<[Year]={$(=Max(Year)-1)}>}  aggr(

if('$(vPerformance)'='Delivery',

if($(vAcceptableDaysDelayed) >= fabs( avg({<[Year]={$(=Max(Year)-1)}>}  aggr( $(eDOT_DiffDel) , $(eAggrLevel1) )) ) ,1,0)

, ),[Purchasedoc], [Purchaseposition]))*100    ,'#.##0,00')

[krishnakv83]

I tried with below formula still getting 0.002.Idon't know what is the issue.How to Debug the issue please show some inputs.

= num(avg({<[Year]={$(=Max(Year)-1)}>}  aggr(

if('$(vPerformance)'='Delivery',

if($(vAcceptableDaysDelayed) >= fabs( avg({<[Year]={$(=Max(Year)-1)}>}  aggr( $(eDOT_DiffDel) , $(eAggrLevel1) )) ) ,1,0)

, ),[Purchasedoc], [Purchaseposition]))*100    ,'#.##0,00')

[Anil_Babu_Samineni]

My last luck would be Cover single Quote (') for Last year in set analysis like Year={'$(=Max(Year)-1)'}

POS - If none of them not getting idea, Please post sample application

[krishnakv83]

With codes it has worked thank you very much Anil for quick response.

Now I got different issue.The above solution is working only when user selects year in list box and zero if year is not selected.we have 3 years in the report.if user not selected year the expression should show avg of 3 year values.Because it not mandatory that user has to select the year.

Is there any way to get this.

Regards,

Venkat

[Anil_Babu_Samineni]

Perhaps this?

=If(GetFieldSelections(Year)>0,num(avg({<[Year]={'$(=Max(Year)-1)'}>}  aggr(

if('$(vPerformance)'='Delivery',

if($(vAcceptableDaysDelayed) >= fabs( avg({<[Year]={$(=Max(Year)-1)'}>}  aggr( $(eDOT_DiffDel) , $(eAggrLevel1) )) ) ,1,0)

, ),[Purchasedoc], [Purchaseposition]))*100    ,'#.##0,00'),

num(avg({<[Year]={">=$(=AddYears(Max(Year),-2))<=$(=Max(Year))"}>}  aggr(

if('$(vPerformance)'='Delivery',

if($(vAcceptableDaysDelayed) >= fabs( avg({<{">=$(=AddYears(Max(Year),-2))<=$(=Max(Year))"}>}  aggr( $(eDOT_DiffDel) , $(eAggrLevel1) )) ) ,1,0), ),[Purchasedoc], [Purchaseposition]))*100    ,'#.##0,00'))