How to make this calculation?

[rcandeo]

I have a big problem and I bet someone can give me a hint for it.

I need to get the balance of each month for every account of the GL system.

For accounts that have entries every month, I need just to take the older entry of each month that has the balance - that´s working.

The problem is if the account has no movimentation for some months or had no more movimentation since a couple of months, but has amount yet.

In the ERP that I´m working, there is no such lauchs for this months, and I need to create in Qlik View a way to show this amount.

Eg:

If the acount 11111 had only entries in months 03, 04 and 11, I need to get a way to create fakes entries in the others months, or some like that.

So if the user wanna know the balance for july/2012, I´ll show him the last amount available, that in the example below, should be 120,00

But if the user wanna know the balance for december/2012, I´ll show him the last amount available, that in the example below, should be 90,00

Acount     year/month/day          amount

11111     2012/03           100,00

11111     2012/04           120,00

11111     2012/11           90,00

Can anyone help me please?

[CELAMBARASAN]

This you need to be in chart or in load script?

Chart means use FirstSortedValue

FirstSortedValue({<DateField={"<=$(=Max(DateField))"}>} amount, -Date)

Hope it helps

[rcandeo]

unfortunatelly I need it in script

[CELAMBARASAN]

Hi

Check with this

Table1:

LOAD Account, Date(MonthStart(Date#(YearMonth,'YYYY/MM')), 'YYYY/MM') AS YearMonth, Amount INLINE [

    Account, YearMonth, Amount

    11111, 2012/03, 100,00

    22222, 2012/05, 99,00

    11111, 2012/04, 120,00

    22222, 2012/08, 125,00

    11111, 2012/11, 90,00

];

Outer Join

LOAD Account , Date(MonthStart(MinDate, IterNo() - 1), 'YYYY/MM') AS YearMonth While Date(MonthStart(MinDate, IterNo() - 1), 'YYYY/MM') <= MaxDate;

Load Account, Min(YearMonth) AS MinDate, Max(YearMonth) As MaxDate

Resident Table1

Group by Account;

CalcTable:

LOAD *, Alt(Amount, Peek(CalcAmount)) AS CalcAmount;

LOAD

*

Resident

Table1

Order by Account, YearMonth;

Drop Table Table1;