4 Replies Latest reply: Dec 20, 2010 2:29 PM by John Witherspoon

# Combining AsOfDate with "normal" date

Hi!

I'm loading values from a qvd-table amongst others: Qty and Sales and Date. I also have an AsOfDate-table that gives me accumulated value.

The problem is that, what I want to do is, I want to load the Qty field and bind it to the AsOfDate-table so the value becomes accumulated when reading it from an AsOfDate. But the Sales-field I don't want to do this with. I understand that I have to load my qvd-file twice into two diffrent tables (one with Qty and one with Sales) and name the Date-field different names in each table with the one in the Qty-table being keyed to the AsOfDate-table.

But, I also want the Qty-table and Sales-table to be keyed by date, so I can get both the Sales (not accumulated) and the Qty (accumulated) togeter.

Whats a good way to solve this? Examples?

Thanks!

• ###### Combining AsOfDate with "normal" date

I think a good way is to keep straight data model and calculate any accumulations within charts.

As for example, this expression calculates accumulation in pivot table from right to left:

RangeSum( before( sum(Qty), 0, ColumnNo() ) )

• ###### Combining AsOfDate with "normal" date

If I understand the problem, just put a DateType field in your AsOf table. Values 'current' and 'accumulated' for instance. Current would only link an AsOfDate to the same Date, while accumulated would link to all dates you're trying to accumulate. Then:

sum({<DateType={'current'}>} Sales)
sum({<DateType={'accumulated'}>} Qty)

Now you can put them both in the same table with dimension AsOfDate, and Sales will only be for the current day, while Qty will be summed across all days in the desired accumulation.

• ###### Combining AsOfDate with "normal" date

`John Witherspoon wrote:If I understand the problem, just put a DateType field in your AsOf table. Values 'current' and 'accumulated' for instance. Current would only link an AsOfDate to the same Date, while accumulated would link to all dates you're trying to accumulate. Then:sum({<DateType={'current'}>} Sales)sum({<DateType={'accumulated'}>} Qty)Now you can put them both in the same table with dimension AsOfDate, and Sales will only be for the current day, while Qty will be summed across all days in the desired accumulation. <div></div>`

Okay! I think I understand the jist of this idea, but how would I implement this DateType field in the script exactly?

My AsOfDate looks like this (I think it was you who gave me this idea to use AsOf btw :)

AsOf:
RESIDENT Transactions;
LEFT JOIN (AsOf)
RESIDENT AsOf;
INNER JOIN (AsOf)
RESIDENT AsOf
WHERE AsOfDate >= Date;

• ###### Combining AsOfDate with "normal" date

Something like this sticking with roughly your current approach:

AsOf:
LOAD DISTINCT Date, 'accumulated' as DateType
RESIDENT Transactions;
LEFT JOIN (AsOf)
RESIDENT AsOf;
INNER JOIN (AsOf)
RESIDENT AsOf
WHERE AsOfDate >= Date;

CONCATENATE (AsOf)
LOAD DISTINCT Date, 'current' as DateType, Date as AsOfDate
RESIDENT Transactions;

But something like this should be more efficient, which would matter if your Transactions table is large (this probably has some syntax or other error somewhere since it's complicated and I'm not testing it):

Calendar:
AUTOGENERATE 1
WHILE len(fieldvalue('Date',iterno()))
;
MinDate:
RESIDENT Calendar
;
LET vMinDate = peek('MinDate')
;
AsOf:
Date as AsOfDate
,'accumulated' as DateType
,date(Date+1-iterno()) as Date
RESIDENT Calendar
WHILE date(Date+1-iterno()) >= \$(vMinDate)
;
CONCATENATE (AsOf)