Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
krishnakv83
Contributor II
Contributor II

Previous year

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

1 Solution

Accepted Solutions
Anil_Babu_Samineni

Great, Now you can do your job !!!

Qlik Community Tip: Marking Replies as Correct or Helpful

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

20 Replies
Anil_Babu_Samineni

Your method looks okay? What is thee issue

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
krishnakv83
Contributor II
Contributor II
Author

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?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
krishnakv83
Contributor II
Contributor II
Author

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
krishnakv83
Contributor II
Contributor II
Author

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
krishnakv83
Contributor II
Contributor II
Author

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)