Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
e0015367
Contributor III
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
Gysbert_Wassenaar

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
Gysbert_Wassenaar

See attached qvw


talk is cheap, supply exceeds demand
e0015367
Contributor III
Contributor III
Author

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 .

Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand

View solution in original post