Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Not applicable

Accumulation in Script

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/yyyyexpenses
01/01/201050
02/01/201055
15/01/201060
20/01/201045
03/02/201015
27/02/201055
09/03/201070
18/03/201030


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.

1 Solution

Accepted Solutions
Highlighted

Re: Accumulation in Script

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.

View solution in original post

10 Replies
Highlighted
Not applicable

Accumulation in Script

HI,

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

Thanks & Regards

Highlighted
Not applicable

Accumulation in Script

Thank you,

But could you please sow this expression in an example?

Highlighted
Not applicable

Accumulation in Script

Hi,

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

Thanks & Regards

Highlighted
Not applicable

Accumulation in Script

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?

Highlighted

Accumulation in Script

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.

Highlighted
Not applicable

Accumulation in Script

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

Highlighted

Re: Accumulation in Script

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.

View solution in original post

Highlighted
Not applicable

Accumulation in Script

Hi, Miguel.

Thank you very much, yes that works.

But here we have

AmountMonthMonthAcum
2141214
8662652
8193167
6554488
3955-93
7516844
926782
2618179
3469167
57610409
64611237
66812431


And what i need is

AmountMonthMonthAcum
2141214
10802866
18993819
25544655
29495395
37006751
46267926
48878261
52339346
580910576
645511646
712312668


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

Highlighted
MVP
MVP

Accumulation in Script

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