How to get cumulative values for each dimension in load

[mazacini]

Hi

I am loading a table of sales transactions.

Fields are:

Date,

Customer,

Invoice Ref,

Sales Value.

I am loading the data for 3 years.

I want to create a field showing cumulative Sales Value for each Customer, for each year.

Any ideas?

EDIT: In fact I think I need a new table showing Customer, Year, Cumulative Sales Valie

[maternmi]

Hi,

try following:

sum({<Customer ={'*'} + date(year(Date, 'YYYY') = {'*'}>} Sales Value)

BR,

Michael

[mazacini]

Hi Michael

Can I use this in my load script?

Joe

[#4 · Accepted solution]

Hi,

You should load a second table order by date and customer, and use the peek () or previous() functions. Sth like:

NOCONCATENATE

LOAD Date, Customer,InvoiceRef,SalesValue

if(Customer =peek('Customer') AND Year(Date) = previous(Year(Date), SalesValues+peek('SalesValue'), Null() ) as 'CumulSalesvalue'

Resident FirstTable

ORDER BY Customer, Date;

Drop FirstTable;

Fabrice

[maternmi]

Hi Joe,

sorry, haven't read your subject correct.  I think Fabrice's suggestion should work.

BR

Michael

[mazacini]

Thank you Fabrice.

Your solution worked - with the following changes:

if(Customer =peek('Customer') AND Year(Date) = previous(Year(Date)), SalesValues+peek('CumulSalesValue'), SalesValueNull() ) as 'CumulSalesvalue'

Many thanks

Can I ask how I might create a table of cumulative value for each customer for the last date in each year that the customer got an invoice for.

So, if a customer's last invoice was in February, it would extract the cumulative value up to February; if last invoice was July, extracts a cumulative value to July.

Again, thanks for your help so far.

Regards

Joe

[#7]

Hi,

In fact, you need to find the maximum month whatever the year:

Before the NOCONCATENATE

LEFT JOIN (MyTable)

LOAD Customer,

    Num(Month(max(Date))) as LastInvoice
RESIDENT MyTable

Group By Customer;

In the NOCONCATENATE statement, in the peek part

add sth like AND Num(Month(Month)) <= LastInvoice

your if statment contains different sub if in order to return either a simple amount (first month for example to initiate the cumulative value) or a null() value  (when Month > LastInvoice)

Please see the qvw and xls files.

There are Prod, Country and Date dimensions. Amounts and Units fields CumulAmounts has been created

P1 is sold until last month (march). P2 is sold only until during january this year even though it has been sold during several months last year. There are two years (2012 and 2013)

Hope this helps

Fabrice

[mazacini]

Hi Fabrice

Unfortunately, I only have personal edition. Could you send me script?

Many thanks for your help.

Regards

Joe

[#9]

of course.

Please find the Word doc with the script out the QVW.

I have created no  object: I just checked the data with the Table Viewer

You can load the previous XLS file to see what I wanted to do. Please adapt the code to your own case.

Fabrice

I have also the Personal Edition

[mazacini]

Hi

Many thanks for your help. Your suggestions led me to a solution.

Cheers

Joe