Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
Partner
Partner

Accumulated sum each year from start to max

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

1 Solution

Accepted Solutions
Partner
Partner

Re: Accumulated sum each year from start to max

Alright, I see what you mean

Try this one

5 Replies
MVP & Luminary
MVP & Luminary

Re: Accumulated sum each year from start to max

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.


talk is cheap, supply exceeds demand
Partner
Partner

Re: Accumulated sum each year from start to max

Hi,

See attached file

Partner
Partner

Re: Accumulated sum each year from start to max

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

Partner
Partner

Re: Accumulated sum each year from start to max

Alright, I see what you mean

Try this one

Partner
Partner

Re: Accumulated sum each year from start to max

Fantastic, works like a charm, much appreciated.