Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
e0015367
New Contributor III

From accounting entries to Opening and Closing balance report

Hi all, I am doing a opening and closing balance report which will look something like this:

   

MonthOpening dateOpening BalClosing dateClosing Bal
Jun 20156/1/201506/30/201563622
Jul 20157/1/2015636227/31/2015138257
Aug 20158/1/20151382578/31/2015138257
Sep 20159/1/20151382579/30/2015138257
Oct 201510/1/201513825710/31/2015219227
Nov 201511/1/201521922711/30/2015385001
Dec 201512/1/201538500112/31/2015476004
Jan 20161/1/20164760041/31/2016314966
Feb 20162/1/20163149662/29/2016132507
Mar 20163/1/20161325073/31/2016230198
Apr 20164/1/20162301984/30/2016269739
May 20165/1/20162697395/31/2016340043
Jun 20166/1/20163400436/30/2016240283
Jul 20167/1/20162402837/31/2016216158
Aug 20168/1/20162161588/31/2016218302
Sep 20169/1/20162183029/30/201693479
Oct 201610/1/20169347910/31/201672991
Nov 201611/1/20167299111/30/201651612
Dec 201612/1/20165161212/31/201616792
Jan 20171/1/2017167921/31/2017

0

I can do this easily in excel but i have no idea where to start if i were to do this in qlikview.

My data table looks something like this :

   

AgentInvoice DatePayment DateAmountInvoice number
B6/6/20152/2/2016636221
A7/25/20151/28/2016746352
A9/1/20159/18/2015493863
E10/31/201511/16/2015809704
B11/5/20152/22/2016773565
E11/28/20152/3/2016829856
A11/28/20151/6/2016864037
C12/4/20158/24/2016372458
A12/13/20152/26/2016207319
B12/27/20155/16/20163302710
F2/3/20168/18/20163482311
A2/3/20169/7/20162741212
B3/14/20166/1/20165560913
C3/16/20167/18/20162412514
D3/17/201611/3/20161795715
B4/14/20166/25/20163954116
C5/27/20169/23/20169374417
A5/31/20166/24/2016958718
E6/15/20169/30/2016497719
A7/2/20167/25/20168262720
A8/15/20169/7/20167421221
E9/5/201611/1/20162920822
B9/27/201610/8/20164631423
D10/12/201611/15/20162582624
A11/8/201612/9/20165161225
A12/8/20161/12/201716792256

Hopefully, i can do this in qlikview's straight table. but  i am stuck in the opening bal's expression. Current my expression is

Sum({< MonthStartDate = {">$(=Date([Invoice Date])<=$(=Date([Payment Date])"} >} Amount)

but nothing is displaying, i wonder whats wrong.

In excel, i can do this easily using this this formula:

=SUMIFS( [amount] , [invoice date] ,"<"&[MonthStartDate],[Payment Date],">="&[MonthStartDate])

i have attached

1) sample data as excel format, inside it contains a desired report format and

2) QVW

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: From accounting entries to Opening and Closing balance report

You can try using the previous and peek functions in the script.


talk is cheap, supply exceeds demand

View solution in original post

3 Replies
MVP & Luminary
MVP & Luminary

Re: From accounting entries to Opening and Closing balance report

See attached qvw


talk is cheap, supply exceeds demand
e0015367
New Contributor III

Re: From accounting entries to Opening and Closing balance report

hi, thanks for the help. is there any other way other than using rangesum and above function? i will like to avoid using rangesum and above function as i want to use this opening and closing figure to calculate another figure.

like

day sales outstanding Days Sales Outstanding - DSO Definition | Investopedia

taking closing balance / total sales for the period , for a particular agent .

MVP & Luminary
MVP & Luminary

Re: From accounting entries to Opening and Closing balance report

You can try using the previous and peek functions in the script.


talk is cheap, supply exceeds demand

View solution in original post