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.

[christian77]

Ok. It´s a good idea but:

201112 + 1 = 201113, not a valid period.

201101 - 1 = 201100, not a valid period.

Table1:

LOAD

year(SalesDate)                                             as Year,

month(SalesDate)                                          as Month,

year(SalesDate) & month(SalesDate)              as Period

from you fact_table;

PrePeriods: 

LOAD

only(Year)                                                     as Year,

only(Month)                                                  as Month,

only(Period)                                             as Period

group by Year, Month, Period

order by Year, Month

from resident Table1;

Periods:

recno()          as Period_id,

Period           as Period

order by Year, Month

resident PrePeriods;

drop table PrePeriods;

i did it too fast, you may have to correct some.

At the end you have a Period_id as an entire number, that you can add and substract as you like. If I want 7 months before, I get seven months before regarding of year changes or anything.

They are many ways of doing that. This is only an example.

Period     Period_id

201111 ----- 1

201112 ----- 2

201201 ----- 3

Do that and you'll win.

The table Periods will never give you circular reference.

bye.

[tanelry]

Of course I can't do 201112 + 1 to find next month, but this format is sufficient for comparison, like when you just need to sum all months before current month (<201108).

[#4]

Simple:)

To find the next yearmonth use this:

IF( RIGHT([yearmonthvalue], 2) = 12 , [yearmonthvalue] + 89 , [yearmonthvalue] + 1 )

To find the yearmonth before use this:

IF( RIGHT([yearmonthvalue], 2) = 01 , [yearmonthvalue] - 89 , [yearmonthvalue] - 1 )

[#5]

Hi guys:

Sorry for the late replay and I finally manage to get the report working.

I will post the Expression below so that if anyone that face similar issue and have a look:-

Opening Balance

sum(if(InventoryDate1<monthsstart(1,varcurrdate) and (invtype='20'),InventoryGoodqty))

-

sum(if(InventoryDate1<monthsstart(1,varcurrdate) and (invtype='34'),InventoryGoodqty))

+

sum(if(InventoryDate1<monthsstart(1,varcurrdate) and (invtype='40'),InventoryGoodqty))

-

sum(if(InventoryDate1<monthsstart(1,varcurrdate) and (invtype='42'),InventoryGoodqty))

Crates To

Sum(if(year = year(varcurrdate)and Inventorymonth1=month(varcurrdate)

            and [InventoryDate1] <= varcurrdate and (invtype='20'),InventoryGoodqty))

+

Sum(if(year = year(varcurrdate)and Inventorymonth1=month(varcurrdate)

            and [InventoryDate1] <= varcurrdate and (invtype='40'),InventoryGoodqty))

Crates From

Sum(if(year = year(varcurrdate)and Inventorymonth1=month(varcurrdate)

            and [InventoryDate1] <= varcurrdate and (invtype='34'),InventoryGoodqty))

+

Sum(if(year = year(varcurrdate)and Inventorymonth1=month(varcurrdate)

            and [InventoryDate1] <= varcurrdate and (invtype='42')and locid='0000',InventoryGoodqty))

MTD Variance

[Crates To (+)] - [Crates From (-)]

Outlet Balance

[Opening Balance] + [MTD Variance]

Hope that it will be able to help someone here.

Guys you all are great please keep up this kind of helpful spirit.

Thank you once again