I have got a pretty general question. We have been working with QlikView for a few months now and while we think it's a good tool for analyzing flow data (additive date) we are wondering how to best work with "stock figures" (amounts that are not additive and cannot simply be aggregated).
On the one hand for flows I have as example "sales". If I have sales on June 3rd and June 15th and June 20th and then I want do to analyze this, it's very easy. If I want to see the flows for June 3rd I get the flows for June 3rd, if i want to see the flows for June as total I just need some month dimension in the data and again it's very easy. Just using the sum() formula will automatically aggregate the data as required.
Now on the other hand suppose I have accounts that have balances for different days. Let's say on June 3rd I have 100 on my bank account, on June 15th I have 200 and on June 20th I have 150 on my bank account.
In the base data we have stored these balances with balance dates and validity dates.
So eg the balance of 100 on June 3rd has a balance date of June 3rd and a validity date of June 15th (because the next valid balance has been entered for June 15th).
Now in other reporting tools it's still rather easy to get the balance that is valid on June 10th. For example in a SQL query I say "where reportingdate between balancedate and validitydate" and voila I get the correct balance of 100 that has been entered on June 3rd but is still valid on June 10th.
Now of course I could also make some SQL query in QlikView and only get me the balances for June 10th, however usually I want to analyze over time, therefore I need more data than that, the whole month of June, probably even the whole year or even more.
And if I want to see the value for a month I cannot simply aggregate it (like for the sales flow data) but I need to do something like display the balance that is valid on the last day of the month (if I decide to analyze on a month level).
How to do this in QlikView? The approach we had is this:
Let's say I have a "time" table (including each day of June, June 1st, June 2nd..... June 30th). Then we have the 3 balances. But because QlikView can only do 1:1 joins we need to replicate the balances (using autogenerate or something) so that we not only have a record on June 3rd for the June 3rd balance but also on June 4th, June 5th.... up to June 15th
For a very small number of accounts this works fine but once we have a higher amount of accounts and want to analyze a longer period of time this leads us to the issue we are facing:
Say I have 5.000 accounts and I have on average 10 balances per year.
Say I want to analyze a timeframe of 3 years.
The base date then is around 150.000 records, which is still an amount that is rather easily manageable.
But using the approach decribed above I now need to replicate the balances so that I not only have those validfrom/validto records but so that I have a balance for each day within the timeframe I want to analyze.
For 5.000 accounts over 3 years this now results in an amount of about 5.5 million records!
In some situations we need to analyze around 20.000 accounts even, then we have over 20 million records. Add that we need to do some additional transformations and also additional tables facing similar issues we are very soon entering double digit amounts of GB of RAM required (and more importantly QlikView letting me wait several seconds after each selection change to reclaculate the table/diagram).
So my question is how do other people do it? Are there smarter ways of working with non-additive figures than replicating the data for each day?
Or don't you use QlikView at all for analysis of non-additive data?
Maybe someone has some examples or can point me in the direction where to find good examples of working with non additive data?
Thanks for any feedback and input on this!