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
