Automate Formula

[SJ_16]

Hi,

Any idea how I can automate the formula below please so I don't have a hard coded date? This will always look at previous year.

RangeSum(Above(Sum({<[Country] = {'UK'}>}if(Left(DATE_IN,4)='2017'
and Lookup='Renewal_Value',Val)),0, RowNo()))
/
RangeSum(Above(Sum({<[Country] = {'UK'}>}if(Left(DATE_IN,4)='2017'
and Lookup='due for renewal' ,Val)),0, RowNo()))

[Somasundaram]

Hi,

Using Addyears() function we can add years/ subtract years. 

RangeSum(Above(Sum({<[Country] = {'UK'}>}if(Left(DATE_IN,4)=Year(AddYears(DATE,-1))
and Lookup='Renewal_Value',Val)),0, RowNo()))
/
RangeSum(Above(Sum({<[Country] = {'UK'}>}if(Left(DATE_IN,4)=Year(AddYears(DATE,-1))
and Lookup='due for renewal' ,Val)),0, RowNo()))

[SJ_16]

Thanks, but this doesn't work as my line on my line chart now disappears.

[Somasundaram]

RangeSum(Above(Sum({<[Country] = {'UK'}>}if(Left(DATE_IN,4)=Year(AddYears(DATE_IN,-1))
and Lookup='Renewal_Value',Val)),0, RowNo()))
/
RangeSum(Above(Sum({<[Country] = {'UK'}>}if(Left(DATE_IN,4)=Year(AddYears(DATE_IN,-1))
and Lookup='due for renewal' ,Val)),0, RowNo()))

[SJ_16]

Still get the same result, the line disappears

[Somasundaram]

Use this one ---------- num(Year(AddYears(DATE_IN,-1)))
hope it will help.

[SJ_16]

Still doesn't work unless I have added it incorrectly?

RangeSum(Above(Sum({<[Country] = {'UK'}>}if(Left(DATE_IN,4)=num(Year(AddYears(DATE_IN,-1)))
and Lookup='Renewal_Value',Val)),0, RowNo()))
/
RangeSum(Above(Sum({<[Country] = {'UK'}>}if(Left(DATE_IN,4)=num(Year(AddYears(DATE_IN,-1)))
and Lookup='due for renewal' ,Val)),0, RowNo()))

[Somasundaram]

If possible can you share the app?

[ahmed_hassan]

 HI,

The best way to do it is by creating 2 variables.  You can type the following code in the script or do it manually.

Set vMaxyear = 'Max(Year(DateField))';
Set vPreyear = 'Max(Year(DateField)-1)';

and then try:

RangeSum(Above(Sum({<[Country] = {'UK'}>}if(Left(DATE_IN,4)='$(vPreyear)'
and Lookup='Renewal_Value',Val)),0, RowNo()))
/
RangeSum(Above(Sum({<[Country] = {'UK'}>}if(Left(DATE_IN,4)='$(vPreyear)'
and Lookup='due for renewal' ,Val)),0, RowNo()))

 Good luck!

[sunny_talwar]

Why don't you use set analysis here

RangeSum(Above(Sum({<[Country] = {'UK'}, DATE_IN_YEAR = {"$(=Max({1}DATE_IN_YEAR)-1)"}, Lookup = {'Renewal_Value'}>} Val), 0, RowNo()))
/
RangeSum(Above(Sum({<[Country] = {'UK'}, DATE_IN_YEAR = {"$(=Max({1}DATE_IN_YEAR)-1)"}, Lookup = {'due for renewal'}>} Val), 0, RowNo()))

Where DATE_IN_YEAR is created in the script like this

LOAD ...,
 DATE_IN,
 Year(DATE_IN) as DATE_IN_YEAR
FROM ...;