Accumulated Account Balance as Line Chart (multi layered problem)

[#1]

Hey everyone,

I'm a QlikView beginner facing an issue with my current dashboard design. I would greatly appreciate if you could help by pointing me in the right direction or providing a better appproach altogether.

Goal

I am trying to display the account balance of different bank accounts (based on selection) over different ranges (also based on selection) in a Line Chart. All data since the beginning is availabe, thus the account balance at a certain point equals the commulative sum of all previous account movements. I also want to be able to change the date dismension depending range selection (e.g. I might choose the year dimension if I selected 10 years but would want to see daily values when only selected a single month)

Approach

• I have an effective date field in my data which is broken down into 'Effective_Year', 'Effective_Month', 'Effective_Period', 'Effective_Week' and 'Effective_Day' in the load for selection and dimension purposes.
  • I defined a variable 'vDateDim' which I implemented in buttons with the aim to switch the date dimension. This doesn't need to be automated.
• Based on Elif's article (https://community.qlik.com/blogs/qlikviewdesignblog/2012/10/12/accumulative-sums), I decided  to use following formula to calculate the accumulated sums:  [=sum(aggr(rangesum(above(total sum({<Effective_Year=>}Acc_Net_C),0,RowNo())),Effective_Year))]
  • Acc_Net_C is the value data field which has to be summarized.
  • I wasn't succesful with earlier Set Analysis approaches
• This formula displays the correct Line Chart for the year dimension (without selection and in the case of only a few consecutive years selected). It fails to provide accurate data for all other date dimensions if the variable is changed in the formula above.
• Elfi highlights the importance of the data being sorted correctly, which I tried to implement with an 'Order by'-expression in the 'Resident Load'.
  • But the data seems unsorted in the preview view!
  • See the load script below (simplified):

TempTable:
LOAD
*
, week(Effective_D) As Effective_Week 
, Year(Effective_D) As Effective_Year
, Date(monthstart(Effective_D), 'MMM-YY') as Effective_Period
, Month(Effective_D) As Effective_Month
, Day(Effective_D) As Effective_Day
FROM
...Journal_Entries.qvd (qvd)
;

Data:
NOCONCATENATE LOAD
*
Resident TempTable
Order by Effective_D;

Question Summary

1) Can I use the above mentioned formula in my line chart or is there a better approach?

2) Any clues why the 'order by' seems to fail?

3) Can I use the vDateDim variable in principle to adjust formula and date dimension through buttons?

Cheers,

Tim