Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
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

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

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?

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

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

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

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)