Previous Month Value

[Riadh]

Hi,

I need to create monthly balance from Jan 2020 to Aug. 2020:

but in some month there is no balance because no transactions has been done.

for the month without data I want to display the previous monthly balance

Find attached data and qvf.

Thank you

[QFabian]

Hi @Riadh !, please try this example :

//you can do this data of every period in multiples ways, for example, with a For Cycle, here is just one, with values direcly in inline

Data:
LOAD * INLINE [
Date
1-1-2020
1-2-2020
1-3-2020
1-4-2020
1-5-2020
1-6-2020
1-7-2020
1-8-2020
];

//then you need to make every data crossing, using this join with all Sccount and months

join
Load distinct Account FROM [lib://desktop (fabian_fabian)/temp previous Month.xlsx]
(ooxml, embedded labels, table is Sheet1);

//then you add the existing data making null cells.

left join

LOAD
Account,
Date,
Balance
FROM [lib://desktop (fabian_fabian)/temp previous Month.xlsx]
(ooxml, embedded labels, table is Sheet1);

//finally you replace null with using peek() and previous()
Data2:
Load
if(isnull(Account), previous(Account), Account) as Account,
// Date,
monthname(Date) as Period,
Balance,
if(isnull(Balance) and Account = previous(Account), peek(Balance2), Balance) as Balance2
Resident Data
order By
Account, Date;
drop table Data;

[Phan_Anh_Son]

Help me get the Open and Balance values.

Thanks.

[marksouzacosta]

Hi @Phan_Anh_Son,

Try this:

TempData:
LOAD
    "Date",
    Account,
    Buy,
    Sales
FROM [lib://DataFiles/DATA_Test_V4.xlsx]
(ooxml, embedded labels, table is Sheet1);

Data:
LOAD
    "Date",
    Account,
    Buy,
    Sales,
    If(Match(Account, Peek(Account)),Peek(Balance),0) AS Open,
    If(Match(Account, Peek(Account)),Peek(Balance),0) + Buy - Sales AS Balance
RESIDENT
 TempData
ORDER BY
 Account,
 Date
;

DROP TABLE TempData;

[Phan_Anh_Son]

Thank you for the solution! This worked exactly as per my requirement.

[Phan_Anh_Son]

Why do I have error when inserting more new data?

huhu 😞

[marksouzacosta]

Hi @Phan_Anh_Son,

That is because you have more than one record for the same day and same Account.
This code should solve it:

TempData:
LOAD
    "Date",
    Account,
    Sum(Buy) AS Buy,
    Sum(Sales) AS Sales
FROM
    [lib://DataFiles/DATA_Test_V4 (1).xlsx]
    (ooxml, embedded labels, table is Sheet1)
Group By
	Date,
    Account
;


Data:
LOAD
    "Date",
    Account,
    Buy,
    Sales,
    If(Match(Account, Peek(Account)),Peek(Balance),0) AS Open,
    If(Match(Account, Peek(Account)),Peek(Balance),0) + Buy - Sales AS Balance
RESIDENT
	TempData
ORDER BY
	Account,
    Date
;

DROP TABLE TempData;

[Phan_Anh_Son]

I am very thankful that you are considering my problem.

[marksouzacosta]

You are very welcome @Phan_Anh_Son