Accumulated Account Balance as Line Chart (multi layered problem)

Thu, 29 Sep 2016 14:49:54 GMT

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

topic Accumulated Account Balance as Line Chart (multi layered problem) in QlikView

Accumulated Account Balance as Line Chart (multi layered problem)