topic Accumulation in Script in QlikView

Accumulation in Script

Mon, 11 Apr 2011 10:46:18 GMT

Hi,

I have a table with expenses and dates.

How can I accumulate the data by months?

For exemple we have a table with the data:

dd/mm/yyyy	expenses
01/01/2010	50
02/01/2010	55
15/01/2010	60
20/01/2010	45
03/02/2010	15
27/02/2010	55
09/03/2010	70
18/03/2010	30

If I select 'Jan' I get 210, If I select 'Feb' i get 70, If I select 'Mar' I get 100.

What i want is, if I select 'Jan' I get 210, If I select 'Feb' i get 280, If I select 'Mar' I get 380.

How can we do this in QV in the Script?

Thank you.

Accumulation in Script

Mon, 11 Apr 2011 10:50:26 GMT

HI,

You can achieve the same by using peek() statement.

Thanks & Regards

Accumulation in Script

Mon, 11 Apr 2011 11:03:10 GMT

Thank you,

But could you please sow this expression in an example?

Accumulation in Script

Mon, 11 Apr 2011 13:24:03 GMT

Hi,

Please find the attached app, hope this will help you.

Thanks & Regards

Accumulation in Script

Mon, 11 Apr 2011 13:45:48 GMT

Hi, Upendra11

Thank you a lot. I got what the peek() expression serves for.

Sorry, but in this exemple date is always a column. But in my practice I may use the month(date) expression and use it as row (because there are a lot of other fields (account, unit, function) to be presented in columns)... May be there is som other way out to accumulate the data on the date basis?

Besides, I just remembered that I have a file, where vice versa I have accumulated data. For example, I get general ledger for January with transactions made in January. In February I get another General Ledger with tranasction from January + Februray. Accumulated. But I need a nonaccumulated data. So from the Feb's Gen Ledger I select the transaction I already had in Janury. May be there is an easier way to do this?

Accumulation in Script

Miguel_Angel_Baeyens — Mon, 11 Apr 2011 14:35:31 GMT

Hello Melisa,

Check this script as an example of getting month accumulation:

Table:LOAD Chr(64 + Ceil(Rand() * 2)) AS Code, Month(Date('01/01/2011') + Ceil(Rand() * 90)) AS Month, Ceil(Rand() * 1000) AS AmountAUTOGENERATE 10; TableStep2:NOCONCATENATE LOAD *RESIDENT TableORDER BY Code, Month; DROP TABLE Table; AcumTable:LOAD *, If(Code = Previous(Code), If(Month = Previous(Month), RangeSum(Amount, Peek('MonthAcum')), Amount), Amount) AS MonthAcumRESIDENT TableStep2; DROP TABLE TableStep2;

In the other case -I may have missed something- how can you non accumulate data? I mean, if you have data only for January, is there any field less than month you can use to divide your amounts into (date, week)? Otherwise, how would you split that amount?

Hope that helps.

Accumulation in Script

Mon, 11 Apr 2011 14:55:25 GMT

Hi, Miguel.

I need some time to understand the expressions you use, so I can tell if it helps only tomorrow. But thank you a lot fir your answer!

Speaking about the another case, ofcourse in January we have the data only for Janury. But in Februry we have for both for Jan and for Feb, so we need to make Feb-Jan=the data only fo Feb. But it is very unconvinient for the next 12 Months

Re: Accumulation in Script

Miguel_Angel_Baeyens — Mon, 11 Apr 2011 15:19:41 GMT

Hello Melisa,

Using the same Previous() and Peek() functions you can do something similar, but instead of adding, substracting this current month's amount to previous month amount

Table:
LOAD IterNo() AS Month, 
     Ceil(Rand() * 1000) AS Amount
AUTOGENERATE 1 WHILE IterNo() < 13; 
TableStep2:
NOCONCATENATE LOAD *
RESIDENT Table
ORDER BY Month; 
DROP TABLE Table; 
AcumTable:
LOAD *, 
     If(Month > Previous(Month), RangeSum(Amount, Peek('MonthAcum') * -1), Amount) AS MonthAcum
RESIDENT TableStep2; 
DROP TABLE TableStep2;

The idea is that Amount in the first table has the value for this and previous months (which in the example above is not correct, since amounts are randomized) and if that the current record's month is greater than previous record, subtract to this month's amount, previous' amount.

With more sensible data, I think it's worth trying.

Hope that helps.

Accumulation in Script

Tue, 12 Apr 2011 06:37:59 GMT

Hi, Miguel.

Thank you very much, yes that works.

But here we have

Amount	Month	MonthAcum
214	1	214
866	2	652
819	3	167
655	4	488
395	5	-93
751	6	844
926	7	82
261	8	179
346	9	167
576	10	409
646	11	237
668	12	431

And what i need is

Amount	Month	MonthAcum
214	1	214
1080	2	866
1899	3	819
2554	4	655
2949	5	395
3700	6	751
4626	7	926
4887	8	261
5233	9	346
5809	10	576
6455	11	646
7123	12	668

May be that is even easier...? Can we do this with the help of PEEK()?

Accumulation in Script

tresB — Tue, 12 Apr 2011 06:58:08 GMT

Hi Melisa,

Yes, you can do it with peek. As Miguel suggested (it might need a basic modification, but the logic is surely fine.). you can find one of my post helpful:

http://community.qlik.com/forums/t/43113.aspx

Regards, tresesco

Accumulation in Script

Miguel_Angel_Baeyens — Tue, 12 Apr 2011 09:31:13 GMT

Hello Melisa,

If the data is as simple as that (one record per month) then yes, the script can be simplified to the following (again, dummy data, may make no sense)

Table:LOAD IterNo() AS Month, Ceil(Rand() * 1000) AS Monthly // Amount per monthAUTOGENERATE 1 WHILE IterNo() < 13; TableStep2: // MonthAcum is current month + previousLOAD *, RangeSum(Monthly, Previous(Monthly)) AS MonthAcum // RangeSum will sum NULL (first record) as zero while + will return NULL RESIDENT TableORDER BY Month; DROP TABLE Table; AcumTable: // Amount is the sum of all MonthAcumLOAD *, RangeSum(MonthAcum, Peek('Amount')) AS AmountRESIDENT TableStep2; DROP TABLE TableStep2;

It needs to be done in three steps because of the Rand(), in your table all steps can be done in the same table.

Hope that helps.