Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
You can try using the previous and peek functions in the script.
See attached qvw
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 .
You can try using the previous and peek functions in the script.