Problem with Crossing over to a new year

[#1]

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.

[#2]

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

[#3]

Could someone please help me?

Thanks alot

[tanelry]

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

[#5]

Hi Tanet Ruutli:

I will try your suggestion and reply in this post.

Thank you once again for your feedback.

[#6]

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...

[tanelry]

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.

[christian77]

Hi:

Use date as filtering field.

Make variables MinDate and MaxDate

Use set analysis Date={'<=$(Maxdate)>$(MinDate)'}

[#9]

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))

[tanelry]

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.