Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
HI,
You can achieve the same by using peek() statement.
Thanks & Regards
Thank you,
But could you please sow this expression in an example?
Hi,
Please find the attached app, hope this will help you.
Thanks & Regards
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?
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.
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
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.
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()?
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