Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Riadh
Contributor
Contributor

Previous Month Value

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


temppppp.png

 

Find attached data and qvf.

 

Thank you

8 Replies
QFabian
Specialist III
Specialist III

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;

QFabian
Phan_Anh_Son
Partner - Contributor III
Partner - Contributor III

Help me get the Open and Balance values.

Thanks.

TestV4.jpg

marksouzacosta
Partner - Specialist
Partner - Specialist

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;

 

Read more at Data Voyagers - datavoyagers.net
Phan_Anh_Son
Partner - Contributor III
Partner - Contributor III

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

 

Phan_Anh_Son
Partner - Contributor III
Partner - Contributor III

Why do I have error when inserting more new data?

huhu 😞

Phan_Anh_Son_0-1720585404387.png

 

marksouzacosta
Partner - Specialist
Partner - Specialist

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;
Read more at Data Voyagers - datavoyagers.net
Phan_Anh_Son
Partner - Contributor III
Partner - Contributor III

I am very thankful that you are considering my problem.

marksouzacosta
Partner - Specialist
Partner - Specialist

You are very welcome @Phan_Anh_Son 

Read more at Data Voyagers - datavoyagers.net