topic Re: Please Help! in QlikView

Please Help!

gaurav2017 — Fri, 29 Jun 2012 08:50:10 GMT

Nobody is there who can solve my problem, or its impossible to do it.

I am restating the problem again:

I have tables:

FiscalCalendar:

Month	Quarter
Jan	Q4
Feb	Q4
Mar	Q4
Apr	Q1
May	Q1
Jun	Q1
Jul	Q2
Aug	Q2
Sep	Q2
Oct	Q3
Nov	Q3
Dec	Q3

StockSummary:

ProductCode	ProductName	Sizes	Month	Year	PrimarySales	SecondarySales
X050	ProductA	S	Jul	2012	143	56
X050	ProductA	S	Aug	2012	128	67
X050	ProductA	S	Sep	2012	155	44
Y100	ProductB	M	Jul	2012	232	55
Y100	ProductB	M	Aug	2012	545	45
Y100	ProductB	M	Sep	2012	897	42
Z150	ProductC	L	Jul	2012	334	57
Z150	ProductC	L	Aug	2012	122	87
Z150	ProductC	L	Sep	2012	86	298
Z150	ProductC	L	Oct	2012	97	15

OpeningSales:

ProductCode	Month	Year	OpningSales
X050	Apr	2012	1000
Y100	Apr	2012	1100
Z150	Apr	2012	1200

Problem:

1. ClosingSales = (OpeningSales+PrimarySales) - SecondarySales

2. ClosingSales (March) = OpeningSales (April)

ClosingSales(April, 2012) = OpeningSales(May, 2012)

& so on.

...........................................................................

& also,

ClosingSales (Mar, 2013) = OpeningSales (April, 2013)

& so on.

................................................................................

.................................................................................

I mean it should behave like this.

Regards,

Gaurav Malhotra

Re: Please Help!

Oleg_Troyansky — Wed, 04 Jul 2012 13:15:14 GMT

I'm not totally sure that I understand the problem...

You use the terminology that belongs to inventory management, such as:

April's closing balance = May's opening balance

May's Closing Balance = Opening Balance + Receiving transactions - Shipping transactions

June Opening Balance = May Closing Balance

For inventory balance calculations, I typically recommend pre-calculating the monthly balances in the load script, using functions previous() and peek(). It's possible to calculate the balances on the fly in chart expressions, but it's more complex and heavier on performance.

The same logic isn't quite applicable to Sales. Sales transactions are simply recorded in the month that they happened. April Sales = sum of Sales that were recorded in April, etc...

If this is a GL calculation of GL balances for the account Sales, then you can use the same logic as for the inventory, only you need to define both the Balances and the Transactions at the same level of detail - either including Size or not including Size. Assuming that you have this information by size, the fragment of the load script to calculate monthly balances could look like the following (assuming that you joined the two tables together):

load

ProductCode,

Size,

Year,

Month,

OpeningBalance,

PrimarySales,

SecondarySales

if( previous(ProductCode) <> ProductCode or Previous(Size) <> Size , Opening Balance,

peek(ClosingBalance) + PrimarySales - SecondarySales) as ClosingBalance,

...

This is just an example of how to calculate running balances, you need to do all the prep work of getting the data into the same table...

This sort of a problem belongs to the Data Modeling training class, that I wll surely develop next after the Set Analysis class (back to our LinkedIn discussion)

cheers,

Oleg

cheers

Re: Please Help!

gaurav2017 — Wed, 04 Jul 2012 13:40:21 GMT

It is basically Stock Analysis.

My boss told me that we have Opening Stock from a Table. This Opening Stock is only maintained by our company once i.e. on the 1st month of the fiscal year. & primary Sales & secondary sales are maintained in other table.

Now we can calculate Remaining Stock by Remaining Stock = [Opening Stock + Primary Sales] - Secondary Sales

Now we are having Remaining Stock for 1st Month

Now This Remaining Stock of 1st month should be equal to the Opening Stock of 2nd month.

How I can do it in QlikView.

Re: Please Help!

Oleg_Troyansky — Wed, 04 Jul 2012 13:46:20 GMT

Gaurav,

yes, so your problem fits the Inventory Balance calculation that I suggested in my earlier message. You need to bring the Opening Balance into the transaction table (using either Join or Mapping), and then reload the table in memory using the RESIDENT load and applying the logic described above. The only thing I forgot to mention is that the load needs to be sorted by ProductCode, Size, Year, Month:

...

ORDER BY

ProductCode,

Size,

Year,

Month

;

Re: Please Help!

Wed, 04 Jul 2012 14:07:13 GMT

Hi Gaurav,

please find the Attached ..

Hope it helps you..

Thanks,

Bhaskar

Re: Please Help!

gaurav2017 — Wed, 04 Jul 2012 14:15:32 GMT

I can't open this qvw as i am using QV Personal Edition. Can you please paste whole thing in text format

Re: Please Help!

Wed, 04 Jul 2012 14:45:17 GMT

//Here is the script :

Mapping

LOAD * INLINE [

Month, MonthNum

Jan, 1

Feb, 2

Mar, 3

Apr, 4

May, 5

Jun, 6

Jul, 7

Aug, 8

Sep, 9

Oct, 10

Nov, 11

Dec, 12

];

LOAD ProductCode,

ProductName,

Sizes,

Month,

Year,

PrimarySales,

SecondarySales

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

Load *,

Date(MakeDate(Year,ApplyMap('M',Month)),'MMM-YYYY') as YearMonth

Resident A;

Drop Table A;

Load

If(Peek(ProductCode)<>ProductCode ,0,Peek(ClosingBalance)) as OpeningBalance,

If(Peek(ProductCode)=ProductCode,Peek(ClosingBalance)+(PrimarySales - SecondarySales),(PrimarySales - SecondarySales)) as ClosingBalance

Resident B Order by ProductCode,YearMonth;

Drop Table B;

Re: Please Help!

gaurav2017 — Mon, 16 Jul 2012 11:51:02 GMT

SET NullDisplay="";

NULLASVALUE *;

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Transactions:

LOAD [Material Code],

Material,

[Depo Location],

Year,

Month,

[Opening Stock]

FROM

(qvd);

Join LOAD [Material Code],

Material,

[Depo Location],

Year,

Month,

[Primary Sales]

FROM

(qvd);

Join LOAD [Material Code],

Material,

[Depo Location],

Year,

Month,

[Secondary Sales]

FROM

(qvd);

MasterData:

LOAD [Plant Code],

[Sales Organisation],

[Depo Location],

Division

FROM

(qvd);

Mapping LOAD [Material Code],

Re: Please Help!

pablolabbe — Mon, 23 Jul 2012 16:08:03 GMT

Gaurav,

If you are a beginner and are trying to do advanced and urgent stuff, the community in this forum can´t help you in required time. All of us are here to share and collaborate not for free consulting.

Have you get a Qlikiview Training first ? There is somebody in your company that have mores Qlikview Skills that could help you ? Your company can afford for consulting services for a few days and help you ?

Community is open to help, we are here to teach you to fish rather than give you the fish. Maybe someone can give you the fish, but could take a while.

Take Care,

Pablo Labbe

Re: Please Help!

gaurav2017 — Tue, 23 Jul 2024 14:54:34 GMT

Hi there,

Thanks a lot for all of you. I finished the Inventory/Stock Analysis Application few days back with all your help. Credit goes to all of you. Most importantly, Mr. Bhaskar Reddy & Mr. Oleg Tyransky. Sorry for rushing as i was doing whole thing in a urgency & needed help.

The correct logic was something like:

If(Peek(Key)<>Key ,test23,Peek([Remaining Stock])) as [Opening Stock],

If(Peek(Key)<>Key,(test23+([Primary Sales] - [Secondary Sales])),Peek([Remaining Stock])+([Primary Sales]-[Secondary Sales])

Resident TransactionsTemp ORDER BY Key, Year, MonthNum;

Regards Gaurav Malhotra

Re: Please Help!

rajni_batra — Tue, 14 May 2013 12:30:13 GMT

Can anybody plz help me with this stock

GRN - GI = Closing

closing + GRN = next month opening

againg Opening -GI = closing for same month

Re: Please Help!

gaurav2017 — Fri, 17 May 2013 09:56:46 GMT

I can't understand exacly, what you want. please explain it in detail plus attach a qvw file.

Month	Quarter
Jan	Q4
Feb	Q4
Mar	Q4
Apr	Q1
May	Q1
Jun	Q1
Jul	Q2
Aug	Q2
Sep	Q2
Oct	Q3
Nov	Q3
Dec	Q3

Month	Quarter
Jan	Q4
Feb	Q4
Mar	Q4
Apr	Q1
May	Q1
Jun	Q1
Jul	Q2
Aug	Q2
Sep	Q2
Oct	Q3
Nov	Q3
Dec	Q3

Month	Quarter
Jan	Q4
Feb	Q4
Mar	Q4
Apr	Q1
May	Q1
Jun	Q1
Jul	Q2
Aug	Q2
Sep	Q2
Oct	Q3
Nov	Q3
Dec	Q3