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):
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)
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.
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:
please find the Attached ..
Hope it helps you..
Opening&ClosingBalance.qvw 144.2 K
//Here is the script :
LOAD * INLINE [
(ooxml, embedded labels, table is Sheet1);
Date(MakeDate(Year,ApplyMap('M',Month)),'MMM-YYYY') as YearMonth
Drop Table A;
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;
SET TimeFormat='h:mm:ss TT';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
LOAD [Material Code],
[C:\Users\test\Desktop\Secondary Sales\QVD Gen\OpeningStock.qvd]
Join LOAD [Material Code],
[C:\Users\test\Desktop\Secondary Sales\QVD Gen\PrimarySales.qvd]
Join LOAD [Material Code],
[C:\Users\test\Desktop\Secondary Sales\QVD Gen\SecondarySales.qvd]
LOAD [Plant Code],
[C:\Users\test\Desktop\Secondary Sales\QVD Gen\MasterData.qvd]
Mapping LOAD [Material Code],
[C:\Users\test\Desktop\Secondary Sales\Customised Sheets\Rajasthan\MappingTable.xlsx]
(ooxml, embedded labels, table is Mapping);
Load [Material Code],
Month & Year as MonthYear,
If(Peek([Material Code])=[Material Code],Peek([Remaining Stock])) as [Opening Stock],
If(Peek([Material Code])=[Material Code],Peek([Remaining Stock])+([Primary Sales] - [Secondary Sales]),([Primary Sales] - [Secondary Sales])) as [Remaining Stock]
Order by Month, Year, [Material Code];
What is wrong in it ???
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.
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 fo being rude as i was doing whole thing in a kind of 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
Can anybody plz help me with this stock
GRN - GI = Closing
closing + GRN = next month opening
againg Opening -GI = closing for same month
TestStocking.xlsx 8.4 K