8 Replies Latest reply: Nov 11, 2010 7:50 AM by mauich123 RSS

    Selective Accumulation

    mauich123

      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
          Deepak Kurup

          HI,

           

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

            • Selective Accumulation
              mauich123

              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
              Jonathan Dienst

              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

                • Selective Accumulation
                  mauich123

                  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
                      John Witherspoon

                      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)