Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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.
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
;
Hi Gaurav,
please find the Attached ..
Hope it helps you..
Thanks,
Bhaskar
I can't open this qvw as i am using QV Personal Edition. Can you please paste whole thing in text format
//Here is the script :
M:
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
];
A:
LOAD ProductCode,
ProductName,
Sizes,
Month,
Year,
PrimarySales,
SecondarySales
FROM
[Book1.xlsx]
(ooxml, embedded labels, table is Sheet1);
B:
Load *,
Date(MakeDate(Year,ApplyMap('M',Month)),'MMM-YYYY') as YearMonth
Resident A;
Drop Table A;
C:
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;
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],
Category
FROM
(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]
Resident Transactions
Order by Month, Year, [Material Code];
What is wrong in it ???
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
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