Selective Accumulation

[#1]

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!

[deepakk]

HI,

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

[#3]

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

[jonathandienst · Accepted solution]

Hi

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

[#5]

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!

[johnw]

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)

[#7]

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?

[johnw]

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
;

[#9]

Thanks! This solved it.