topic Selective Accumulation in QlikView

Selective Accumulation

Sat, 06 Nov 2010 15:06:59 GMT

I know this has been brought up here before but I can't find any good answers.

To put it simply: I have a table with 2 fields, one value field and one date field. I have these fields in a straight table with full accumulation.

What I want to do is to be able to select a date range in the middle of the table and still have the accumulation intact (not beginning on 0 again). I've been trying with Sets etc but can't get it to work. My idea was to have a set that sums up all the previous values and then add this number to the new accumulation, but it doesnt work.

Any ideas? I would rather not have a seperate date field to control it either

Thanks!

Selective Accumulation

deepakk — Sun, 07 Nov 2010 04:55:05 GMT

HI,

Try to Accumulate the data at the script level. I am attaching a sample application. Hope this helps u out.

Selective Accumulation

Mon, 08 Nov 2010 13:01:30 GMT

Hi yes I've thought about this but the problem is it would be ~10 million rows to accumulate for my project and it would take a lot of time it seems like 😉

Any other ideas? I thought about if you select a date range in a table it would add the previous accumulation using

Sum ( {$<Date = {"<$(FromDate)"}>) Value)

But this just gives me totally wrong values

Selective Accumulation

jonathandienst — Mon, 08 Nov 2010 14:09:17 GMT

You can't use sets for this purpose as the set selection is applied to the whole dataset, and not at the individual dimensions.

You must use something like expression YTD = Rangesum(Sales, above(YTD)), but this will also start with zero unless you add an offset of the YTD to the minimum date in your chart.

The best approach is as the previous poster said to do it in script. Although it will take a little while to run, it is the only the reload time that is affected, so it does not affect the users opening files. If you have 10m records, you should be loading with an incremental load anyway, so if you do daily load, you are just calculating out the values for one day and reading the rest from a QVD file.

Jonathan

Selective Accumulation

Mon, 08 Nov 2010 16:18:36 GMT

Ok cool,

So if I have the following fields for each record: Article, WarehouseNr, Amount, Date and I want an accumulated value for each article for each warehouse. How would I accomplish this?

Thanks!

Selective Accumulation

johnw — Mon, 08 Nov 2010 22:17:17 GMT

Is your objection to a separate date field due to performance? I agree that the common solution of using a date island and matching things up with an if() would have serious performance problems when you have over 10 million rows. But not ALL date-based solutions will have problems. My normal suggestion for cases like this is an AsOf table:

AsOfDate, Date
2010 Jan 1, 2010 Jan 1
2010 Jan 2, 2010 Jan 1
2010 Jan 2, 2020 Jan 2
2010 Jan 3, 2010 Jan 1
2010 Jan 3, 2010 Jan 2
2010 Jan 3, 2010 Jan 3
etc.

Then when you use AsOfDate instead of Date, your data will be automatically accumulated by QlikView's normal relational logic. That should prevent any serious performance problems (I hope). It will work properly if you select a date range in the middle of the table, because every one of those AsOfDates is connected to all the right dates in data, and you haven't excluded those dates.

If I understood the chart you want, it would then look like this:

dimension 1 = Article
dimension 2 = WarehouseNr
dimension 3 = AsOfDate
expression = sum(Amount)

Selective Accumulation

Tue, 09 Nov 2010 15:34:36 GMT

Great solution! But how would I formulate that in the script? I'm quite new to QV 🙂 I have a field called Date which I load from a QVD-file, how do I create your AsOf-table from that?

Selective Accumulation

johnw — Tue, 09 Nov 2010 17:57:34 GMT

Perhaps something like this:

// First, add all dates to the new table.
AsOf:
LOAD Date
RESIDENT YourCalendarTable // assuming you have one
;
// Then join every date to every other date.
LEFT JOIN (AsOf)
LOAD Date as AsOfDate
RESIDENT AsOf
;
// Then only keep the date combinations that support the accumulation.
INNER JOIN (AsOf)
LOAD *
RESIDENT AsOf
WHERE AsOfDate >= Date
;

Selective Accumulation

Thu, 11 Nov 2010 12:50:13 GMT

Thanks! This solved it.