topic How to get cumulative values for each dimension in load in QlikView

How to get cumulative values for each dimension in load

mazacini — Mon, 09 Dec 2013 11:17:28 GMT

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

Re: How to get cumulative values for each dimension in load

maternmi — Mon, 09 Dec 2013 11:30:46 GMT

Hi,

try following:

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

BR,

Michael

Re: How to get cumulative values for each dimension in load

mazacini — Mon, 09 Dec 2013 11:35:26 GMT

Hi Michael

Can I use this in my load script?

Joe

Re: How to get cumulative values for each dimension in load

Mon, 09 Dec 2013 11:45:41 GMT

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

Re: How to get cumulative values for each dimension in load

maternmi — Mon, 09 Dec 2013 12:09:53 GMT

Hi Joe,

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

Michael

Re: How to get cumulative values for each dimension in load

mazacini — Mon, 09 Dec 2013 12:11:47 GMT

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

Re: Re: How to get cumulative values for each dimension in load

Mon, 09 Dec 2013 15:11:55 GMT

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

Re: How to get cumulative values for each dimension in load

mazacini — Mon, 09 Dec 2013 20:43:45 GMT

Hi Fabrice

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

Many thanks for your help.

Regards

Joe

Re: Re: How to get cumulative values for each dimension in load

Mon, 09 Dec 2013 20:49:35 GMT

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

Re: Re: How to get cumulative values for each dimension in load

mazacini — Thu, 12 Dec 2013 22:07:05 GMT

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

Cheers

Joe