Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
To all those helpful guys here:
For 30/11/2007 the Balance is the Opening Balance for 01/12/2007.
All this work fine for 2007 but when it cross over to the new year 2008.
The Opening Balance and Creates to and Crates From all look funny.
I was thinking maybe something wrong with the Expression that I did not take or forget to include.
I will inculde the Expression below.
varcurrdate
=makedate(right(CurrentDate,4), mid(CurrentDate,4,2), left(CurrentDate,2))
My Opening Balance Expression:-
(
(Sum (if(year(InventoryDate1)<=(year(varcurrdate))
and Inventorymonth1<month(varcurrdate)
and (InventoryDate1< varcurrdate)
and (invtype='20'),InventoryGoodqty)))
-
(Sum (if(year(InventoryDate1)<=(year(varcurrdate))
and Inventorymonth1<month(varcurrdate)
and (InventoryDate1< varcurrdate)
and (invtype='34'),InventoryGoodqty)))
)
+
(
(Sum (if((year(InventoryDate1)<=year(varcurrdate))
// and (InventoryDate1< varcurrdate)
//and Inventorymonth1<month(varcurrdate)
and (invtype='40') and locid='0000'
,InventoryGoodqty)))
-
(Sum (if((year(InventoryDate1)<=year(varcurrdate))
and (invtype='42') and locid='0000'
,InventoryGoodqty)))
Crate To Expression:
(Sum (if(year(InventoryDate1)<=(year(varcurrdate))
and Inventorymonth1=month(varcurrdate)
and (InventoryDate1<= varcurrdate)
and (invtype='20'),InventoryGoodqty)))
+
(Sum (if((year(InventoryDate1)=year(varcurrdate))
and Inventorymonth1=month(varcurrdate)
and (invtype='42') and locid='0000'
,InventoryGoodqty)))
Crate From Expression
(Sum (if(year(InventoryDate1)<=(year(varcurrdate))
and Inventorymonth1=month(varcurrdate)
and (InventoryDate1<= varcurrdate)
and (invtype='34'),InventoryGoodqty)))
+
(Sum (if((year(InventoryDate1)=year(varcurrdate))
// and (InventoryDate1< varcurrdate)
and Inventorymonth1=month(varcurrdate)
and (invtype='40') and locid='0000'
,InventoryGoodqty)))
Below is From the Edit Script:
Main:
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
//let todaydate= today(0);
//Let Startdate = year(today(0)-1) & num(month(today(0)-1),'00') & num(day(today(0)-1),'00');
//SET HIDEPREFIX = %;
SET Today = today(); // Todays date, format YYYY-MM-DD
Let CurrentDate = today();
Let CurrYr = year(today());
Let CurrentYearWeek = year(today()) & num(week(today()),'00');
tbinventorytrans:
Load InventoryKey1,
InventoryDate,
Year(InventoryDate) as Inventoryyear1,
num(Month(InventoryDate),'00') as Inventorymonth1,
Year(InventoryDate) & num(Month(InventoryDate)) as InventoryYearMonth1,
Day(InventoryDate) as Inventoryday1,
date(daystart(InventoryDate),'DD-MM-YYYY') as InventoryDate1,
InventoryStatus,
InventoryGoodqty,
InventoryBadqty,
InventoryQcqty,
prodid,
custacc,
invtype,
locid
FROM
(qvd);// where $(CurrYr) >=#[Inventoryyear1];
I will try to post soem tables later because it seems that there is a problem with my post.
Sorry Guys below is the Tables of the data:
Thank you guys for your patience and help.
Date 01/01/2008:-
Outlet_ID_Name | Crate_ID | Opening Bal | Crates To | Crates From | MTD Variance | Balance |
A0001-2000 NTUC HQ | Big Crate | -183 | 0 | 0 | 0 | -183 |
A0002-2000 NTUC BISHAN A | Big Crate | -16 | 503 | 515 | -12 | -28 |
A0003-2000 NTUC DAWNSON | Big Crate | 64 | 332 | 348 | -16 | 48 |
A0004-2000 NTUC JUNCTION 8 | Big Crate | 30 | 774 | 828 | -54 | -24 |
A0005-2000 NTUC ORCHARD | Big Crate | 90 | 283 | 291 | -8 | 82 |
A0006-2000 NTUC QUEENSTOWN | Big Crate | -29 | 276 | 279 | -3 | -32 |
A0007-2000 NTUC THOMSON | Big Crate | -26 | 634 | 661 | -27 | -53 |
A0008-2000 NTUC TOA PAYOH A | Big Crate | -140 | 410 | 433 | -23 | -163 |
A0009-2000 NTUC TOA PAYOH HDB | Big Crate | 158 | 974 | 1044 | -70 | 88 |
A0010-2000 NTUC BT HO SWEE | Big Crate | 33 | 272 | 267 | 5 | 38 |
A0011-2000 NTUC CORONATION | Big Crate | -78 | 401 | 400 | 1 | -77 |
A0012-2000 NTUC TIONG BAHRU | Big Crate | 76 | 756 | 824 | -68 | 8 |
A0013-2000 NTUC SHAW PLAZA | Big Crate | 54 | 374 | 344 | 30 | 84 |
A0014-2000 NTUC BT PANJANG | Big Crate | -507 | 844 | 888 | -44 | -551 |
Date 31/12/2007
Outlet_ID_Name | Crate_ID | Opening Bal | Crates To | Crates From | MTD Variance | Balance |
A0002-2000 NTUC BISHAN A | Big Crate | 102 | 407 | 406 | 1 | 103 |
A0003-2000 NTUC DAWNSON | Big Crate | 68 | 207 | 209 | -2 | 66 |
A0004-2000 NTUC JUNCTION 8 | Big Crate | 88 | 692 | 709 | -17 | 71 |
A0005-2000 NTUC ORCHARD | Big Crate | 25 | 232 | 243 | -11 | 14 |
A0006-2000 NTUC QUEENSTOWN | Big Crate | 92 | 207 | 214 | -7 | 85 |
A0007-2000 NTUC THOMSON | Big Crate | 116 | 514 | 523 | -9 | 107 |
A0008-2000 NTUC TOA PAYOH A | Big Crate | 144 | 380 | 402 | -22 | 122 |
A0009-2000 NTUC TOA PAYOH HDB | Big Crate | -12 | 844 | 856 | -12 | -24 |
A0010-2000 NTUC BT HO SWEE | Big Crate | 69 | 208 | 220 | -12 | 57 |
A0011-2000 NTUC CORONATION | Big Crate | 118 | 317 | 312 | 5 | 123 |
A0012-2000 NTUC TIONG BAHRU | Big Crate | 96 | 626 | 600 | 26 | 122 |
A0013-2000 NTUC SHAW PLAZA | Big Crate | 110 | 326 | 337 | -11 | 99 |
A0014-2000 NTUC BT PANJANG | Big Crate | 360 | 758 | 756 | 2 | 362 |
Date: 01/12/2007
Outlet_ID_Name | Crate_ID | Opening Bal | Crates To | Crates From | MTD Variance | Balance |
A0002-2000 NTUC BISHAN A | Big Crate | 102 | 5 | 3 | 2 | 104 |
A0003-2000 NTUC DAWNSON | Big Crate | 68 | 0 | 0 | 0 | 68 |
A0004-2000 NTUC JUNCTION 8 | Big Crate | 88 | 16 | 13 | 3 | 91 |
A0005-2000 NTUC ORCHARD | Big Crate | 25 | 0 | 0 | 0 | 25 |
A0006-2000 NTUC QUEENSTOWN | Big Crate | 92 | 24 | 23 | 1 | 93 |
A0007-2000 NTUC THOMSON | Big Crate | 116 | 7 | 8 | -1 | 115 |
A0008-2000 NTUC TOA PAYOH A | Big Crate | 144 | 0 | 0 | 0 | 144 |
A0009-2000 NTUC TOA PAYOH HDB | Big Crate | -12 | 16 | 25 | -9 | -21 |
A0010-2000 NTUC BT HO SWEE | Big Crate | 69 | 0 | 0 | 0 | 69 |
A0011-2000 NTUC CORONATION | Big Crate | 118 | 0 | 0 | 0 | 118 |
A0012-2000 NTUC TIONG BAHRU | Big Crate | 96 | 63 | 37 | 26 | 122 |
A0013-2000 NTUC SHAW PLAZA | Big Crate | 110 | 0 | 0 | 0 | 110 |
A0014-2000 NTUC BT PANJANG | Big Crate | 360 | 65 | 47 | 18 | 378 |
Date: 30/11/2007
Outlet_ID_Name | Crate_ID | Opening Bal | Crates To | Crates From | MTD Variance | Balance |
A0002-2000 NTUC BISHAN A | Big Crate | 83 | 511 | 492 | 19 | 102 |
A0003-2000 NTUC DAWNSON | Big Crate | 46 | 315 | 293 | 22 | 68 |
A0004-2000 NTUC JUNCTION 8 | Big Crate | 65 | 795 | 772 | 23 | 88 |
A0005-2000 NTUC ORCHARD | Big Crate | 26 | 298 | 299 | -1 | 25 |
A0006-2000 NTUC QUEENSTOWN | Big Crate | 77 | 266 | 251 | 15 | 92 |
A0007-2000 NTUC THOMSON | Big Crate | 122 | 627 | 633 | -6 | 116 |
A0008-2000 NTUC TOA PAYOH A | Big Crate | 111 | 502 | 469 | 33 | 144 |
A0009-2000 NTUC TOA PAYOH HDB | Big Crate | -17 | 1036 | 1031 | 5 | -12 |
A0010-2000 NTUC BT HO SWEE | Big Crate | 67 | 276 | 274 | 2 | 69 |
A0011-2000 NTUC CORONATION | Big Crate | 129 | 399 | 410 | -11 | 118 |
A0012-2000 NTUC TIONG BAHRU | Big Crate | 97 | 827 | 828 | -1 | 96 |
A0013-2000 NTUC SHAW PLAZA | Big Crate | 80 | 409 | 379 | 30 | 110 |
A0014-2000 NTUC BT PANJANG | Big Crate | 525 | 893 | 1058 | -165 | 360 |
Could someone please help me?
Thanks alot
I would simplify all comparisons in the expression by using InventoryYearMonth field instead of "Year and Month" pairs.
For example replace this
if(year(InventoryDate1)<=(year(varcurrdate)) and Inventorymonth1<month(varcurrdate)
with this
if(InventoryYearMonth1 < varCurrYearMonth
Hi Tanet Ruutli:
I will try your suggestion and reply in this post.
Thank you once again for your feedback.
Hi Tanel Ruutli:
Sorry for the late reply as I try yoru suggestion butit is not working
Not sure what is the issue...
Thanks for your kind help and your reply...
Sure you can make it work.
Did you create calculated variable varCurrYearMonth as: =Year(varcurrdate)&num(Month(varcurrdate))
Then do you get result with simple expression like: sum(if(InventoryYearMonth1 < varCurrYearMonth, InventoryGoodqty))
Going step by step you will find the cause.
Hi:
Use date as filtering field.
Make variables MinDate and MaxDate
Use set analysis Date={'<=$(Maxdate)>$(MinDate)'}
Hi Tanel Ruutli:
Thank you so much for your encouragement. I almost want to give up already.
I try your sugesstion as below.
But was return with scrip Error.
The varcurrdate is writen in the input box Expression.
Maybe that is why I am having the error.
Please share some though.
I will continue to look at the problem.
Thank you once again for your encouragement.
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
//let todaydate= today(0);
//Let Startdate = year(today(0)-1) & num(month(today(0)-1),'00') & num(day(today(0)-1),'00');
//SET HIDEPREFIX = %;
SET Today = today(); // Todays date, format YYYY-MM-DD
Let CurrentDate = today();
Let CurrYr = year(today());
Let CurrentYearWeek = year(today()) & num(week(today()),'00');
Let varcurrYearMonth = year(varcurrdate) & num(month(varcurrdate))
==========
In put Box Expression:
varcurrdate
=makedate(right(CurrentDate,4), mid(CurrentDate,4,2), left(CurrentDate,2))
I meant to create variable varCurrYearMonth not in script but in the menu Settings>Variable Overview, using Definition as:
=Year(varcurrdate)&num(Month(varcurrdate))
This way it will recalculate when user changes date in the inputbox.