Accumulated sum each year from start to max

[muhammadraza]

Hello Guys,

I have a requirement that I want to show accumulated sum each year from start 1/1/(year) to max e.g.

if there is no selection and my data has has max 2015 Oct 20, then each year should show the sum from Jan 1 to Oct 20 e.g.

Year     Value                    Between

2011     some_value        Jan 1 2011 to Oct 20 2011

2012     some_value        Jan 1 2012 to Oct 20 2012

2013     some_value        Jan 1 2013 to Oct 20 2013

2014     some_value        Jan 1 2014 to Oct 20 2014

2015     some_value        Jan 1 2015 to Oct 20 2015

If I have selected from 2011 to 2014, as I have 31 Dec 2014 as Max date then all other years would be showing the sum between the respective duration

Year     Value                    Between

2011     some_value        Jan 1 2011 to Dec 31 2011

2012     some_value        Jan 1 2012 to Dec 31 2012

2013     some_value        Jan 1 2013 to Dec 31 2013

2014     some_value        Jan 1 2014 to Dec 31 2014

I am almost near but I am not able to convert my variable vMax into date and use it for my purpose. PFA are the qvw and xlsx files.

Please provide some guidance.

Best Regards,

Muhammad Raza

[Gysbert_Wassenaar]

Perhaps this document helps: Calculating rolling n-period totals, averages or other aggregations. You can find a YTD example in the AsOf Table Examples.qvw qlikview file attached to the document.

[yura_ratu]

Hi,

See attached file

[muhammadraza]

You start date is fine but end date is wrong, the end date month and day, should be taken from the max date in the selection, like in my data set my max available date is 10/20/2015, so for each year the end date's (day and month) will be 20 and 10, and for each year I will take sum between these dates as I have mentioned. I hope you got my point.

Year    Value                    Between

2011    some_value        Jan 1 2011 to Oct 20 2011

2012    some_value        Jan 1 2012 to Oct 20 2012

2013    some_value        Jan 1 2013 to Oct 20 2013

2014    some_value        Jan 1 2014 to Oct 20 2014

2015    some_value        Jan 1 2015 to Oct 20 2015

Thanks and regards,

Muhammad Raza

[yura_ratu · Accepted solution]

Alright, I see what you mean

Try this one

[muhammadraza]

Fantastic, works like a charm, much appreciated.