topic Re: How to make this calculation? in QlikView

How to make this calculation?

rcandeo — Thu, 20 Dec 2012 16:26:38 GMT

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?

Re: How to make this calculation?

CELAMBARASAN — Thu, 20 Dec 2012 17:13:00 GMT

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

Chart means use FirstSortedValue

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

Hope it helps

Re: How to make this calculation?

rcandeo — Thu, 20 Dec 2012 17:45:55 GMT

unfortunatelly I need it in script

Re: How to make this calculation?

CELAMBARASAN — Thu, 20 Dec 2012 20:45:05 GMT

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;