topic Re: Previous year in QlikView

Previous year

krishnakv83 — Wed, 10 Jan 2018 06:41:10 GMT

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

Re: Previous year

Anil_Babu_Samineni — Wed, 10 Jan 2018 06:43:23 GMT

Your method looks okay? What is thee issue

Re: Previous year

krishnakv83 — Wed, 10 Jan 2018 06:58:18 GMT

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

Re: Previous year

Anil_Babu_Samineni — Wed, 10 Jan 2018 07:01:52 GMT

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

Re: Previous year

krishnakv83 — Wed, 10 Jan 2018 07:08:45 GMT

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

Re: Previous year

Anil_Babu_Samineni — Wed, 10 Jan 2018 07:24:06 GMT

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

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

Re: Previous year

krishnakv83 — Wed, 10 Jan 2018 09:08:52 GMT

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

Re: Previous year

Anil_Babu_Samineni — Wed, 10 Jan 2018 09:12:41 GMT

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

Re: Previous year

krishnakv83 — Wed, 10 Jan 2018 11:19:05 GMT

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

Re: Previous year

Anil_Babu_Samineni — Wed, 10 Jan 2018 11:25:59 GMT

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

Re: Previous year

krishnakv83 — Thu, 11 Jan 2018 06:54:18 GMT

I tried below code.As field selection is null the vlaue in the expression is zero.

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

Re: Previous year

Anil_Babu_Samineni — Thu, 11 Jan 2018 06:56:18 GMT

Because, there is no condition for non-selected one.

Re: Previous year

krishnakv83 — Thu, 11 Jan 2018 06:58:40 GMT

Can we write a code like if the get field selection is null the default consider year is max (year).hence based on year the below expression should work.Because I can not always select one value for year.

Is it possible to define ?

Re: Previous year

Anil_Babu_Samineni — Thu, 11 Jan 2018 07:08:07 GMT

Off course, Why Not?

Red part is for selection, If you select 1 and more it will show Max year data that is green part else blue, It will show all values which are selections

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

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

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

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

num(avg(aggr(

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

if($(vAcceptableDaysDelayed) >= fabs( avg(aggr( $(eDOT_DiffDel) , $(eAggrLevel1) )) ) ,1,0), ),[Purchasedoc], [Purchaseposition]))*100 ,'#.##0,00'))

Re: Previous year

krishnakv83 — Thu, 11 Jan 2018 07:31:09 GMT

Yes your code is working for me.Thank you

But I'm little skeptical to use this big code in the front end.Because I need to use this formula $(vPerformance)'='Delivery', in 9 places 1 st tab and $(vPerformance)'='confirmation', 9 places in 2nd tab.

I guess this calculation is burden in the front end and leads to performance issue in future.

Regards,

Venkat

Re: Previous year

Anil_Babu_Samineni — Thu, 11 Jan 2018 07:33:24 GMT

I don't think so, It won't kill the performance. Are you getting anything related performance problem?

Re: Previous year

krishnakv83 — Thu, 11 Jan 2018 08:02:55 GMT

This report has 6 tabs and mulyi langa.. dashboard.Supporting 4 countries.few charts are taking time to load.

I'm thing on the solution.

Regards,

Venkat

Re: Previous year

Anil_Babu_Samineni — Thu, 11 Jan 2018 08:51:28 GMT

Sure, Instead Front end, create one Master Parameter table for multi languages supporting to USERS. That makes sense to keep update upon stage..

Re: Previous year

krishnakv83 — Fri, 12 Jan 2018 06:27:51 GMT

I don'know I'm using solution and creating the report.We will see if face any performance issue.

As of now Providing the solution is more important

Regards,

Venkat

Re: Previous year

Anil_Babu_Samineni — Fri, 12 Jan 2018 06:32:52 GMT

Great, Now you can do your job !!!

Qlik Community Tip: Marking Replies as Correct or Helpful