Opening Balance For A new Year without using Set A... - Qlik Community

Report Inappropriate Content · ‎2011-08-23

Hi Guys:

I am able to get the Opening Balance (For whole of 2007) with the following Expression But once reach 01^st January 2008 the Opening Balance is not correct.

Do I need to add anything (like makeyear or makemonth function for the below Expression) if need to can please show me the light?

My Opening Balance 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 Inventorymonth1<month(varcurrdate)

and (InventoryDate1< varcurrdate)

and (invtype='20'),InventoryGoodqty)))

)

+

(

(Sum (if((year(InventoryDate1)<=year(varcurrdate))

and (invtype='40') and locid='0000'

,InventoryGoodqty)))

-

(Sum (if((year(InventoryDate1)<=year(varcurrdate))

and (invtype='42') and locid='0000'

,InventoryGoodqty)))

)

Thank you guys...

SunilChauhan · ‎2011-08-23

better to share some sample

Sunil Chauhan

Report Inappropriate Content · ‎2011-08-23

Agreed. Create a sample qlikview document and upload. There are many reasons why it may not be working

Report Inappropriate Content · ‎2011-08-23

Hi Byron_Van_wy and Sunil Chauhan:

Thanks for your reply.Below is the Sample.

For 30/11/2007 the Balance is the Opening Balance for 01/12/2007.

All this work find 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.

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

My Opening Balance 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 Inventorymonth1<month(varcurrdate)

and (InventoryDate1< varcurrdate)

and (invtype='20'),InventoryGoodqty)))

)

+

(

(Sum (if((year(InventoryDate1)<=year(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)))

Guys Thanks alot

Report Inappropriate Content · ‎2011-08-23

Hello Guys:

I am thinking it is better to include the Edit Script as well so that all helpful guys here can have a better understanding.

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

Opening Balance For A new Year without using Set Analysis.