Current Year and Previous Year sales set analysis in a table

[sagarjagga]

Hi All,

 i have a requirement where i need to display the current year and previous year amt in a table corresponding to the year.

i.e below is my data set 

Year	Amt
2015	100
2016	200
2018	300
2019	400

My desired output is 

Year	current year amt	previous year amt
2015	100	0
2016	200	100
2018	300	200
2019	400	300

Through script , it is easily possible. Above function will also work but if the order of the Year has been changed then it will not work. Looking to do in the Set Analysis itself

[Kashyap_R]

Hi 

Try this

if(ISNULL(Above(Sum([qq.Amt]),1)),0,Above(Sum([qq.Amt]),1))

Hope it helps 

Thanks

[lanlizgu]

You can add in the table a dimension as follow

=date(AddYears( date#(Year, 'YYYY'),-1),'YYYY')

so you could display the previous year with the Amt. The thing is that the previous year of 2018 is 2017, not 2016 as you are expecting. If so, you could add to the table in the script a column with the previous year that you are expecting and display this new field as the dimension in the table.

[sagarjagga]

Thanks for your Response Kashyap .

Any other function other than above?

[Kashyap_R]

Hi 

try doing it in the back end using peek function and create a new column as previous.

Hope this helps 

Thanks