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