From accounting entries to Opening and Closing balance report

[Anonymous]

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

Month	Opening date	Opening Bal	Closing date	Closing Bal
Jun 2015	6/1/2015	0	6/30/2015	63622
Jul 2015	7/1/2015	63622	7/31/2015	138257
Aug 2015	8/1/2015	138257	8/31/2015	138257
Sep 2015	9/1/2015	138257	9/30/2015	138257
Oct 2015	10/1/2015	138257	10/31/2015	219227
Nov 2015	11/1/2015	219227	11/30/2015	385001
Dec 2015	12/1/2015	385001	12/31/2015	476004
Jan 2016	1/1/2016	476004	1/31/2016	314966
Feb 2016	2/1/2016	314966	2/29/2016	132507
Mar 2016	3/1/2016	132507	3/31/2016	230198
Apr 2016	4/1/2016	230198	4/30/2016	269739
May 2016	5/1/2016	269739	5/31/2016	340043
Jun 2016	6/1/2016	340043	6/30/2016	240283
Jul 2016	7/1/2016	240283	7/31/2016	216158
Aug 2016	8/1/2016	216158	8/31/2016	218302
Sep 2016	9/1/2016	218302	9/30/2016	93479
Oct 2016	10/1/2016	93479	10/31/2016	72991
Nov 2016	11/1/2016	72991	11/30/2016	51612
Dec 2016	12/1/2016	51612	12/31/2016	16792
Jan 2017	1/1/2017	16792	1/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 :

Agent	Invoice Date	Payment Date	Amount	Invoice number
B	6/6/2015	2/2/2016	63622	1
A	7/25/2015	1/28/2016	74635	2
A	9/1/2015	9/18/2015	49386	3
E	10/31/2015	11/16/2015	80970	4
B	11/5/2015	2/22/2016	77356	5
E	11/28/2015	2/3/2016	82985	6
A	11/28/2015	1/6/2016	86403	7
C	12/4/2015	8/24/2016	37245	8
A	12/13/2015	2/26/2016	20731	9
B	12/27/2015	5/16/2016	33027	10
F	2/3/2016	8/18/2016	34823	11
A	2/3/2016	9/7/2016	27412	12
B	3/14/2016	6/1/2016	55609	13
C	3/16/2016	7/18/2016	24125	14
D	3/17/2016	11/3/2016	17957	15
B	4/14/2016	6/25/2016	39541	16
C	5/27/2016	9/23/2016	93744	17
A	5/31/2016	6/24/2016	9587	18
E	6/15/2016	9/30/2016	4977	19
A	7/2/2016	7/25/2016	82627	20
A	8/15/2016	9/7/2016	74212	21
E	9/5/2016	11/1/2016	29208	22
B	9/27/2016	10/8/2016	46314	23
D	10/12/2016	11/15/2016	25826	24
A	11/8/2016	12/9/2016	51612	25
A	12/8/2016	1/12/2017	16792	256

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

[Gysbert_Wassenaar]

See attached qvw

[Anonymous]

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 · Accepted solution]

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