Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
Firstly download & open the file i attached.
then i want to know if its possible to find "Stock" & if yes then how ???
The scenario:
I am having fields:
A,B,C,D,E,F,PrimarySales, SecondarySales
Given:
I have OpeningStock for 1st month of fiscal year i.e. July
I have PimarySales & SecondarySales of every month in a year.
Required:
For July,
The RemainingStock should be calculated by:
RemainingStock = (OpeningSales+PrimarySales) - SecondarySales
For August,
RemainingStock of July = OpeningStock of Aug
Regards,
Gaurav Malhotra
Hi Gaurav,
give me some time i will come with the solution
Regards,
Prasath
Hi All,
First Pls explain that code segments.
Regards
Gaurav Malhotra
Hi,
Actually in your case you want to create the calendar with the month in the sorting order because, defalut sorting order will be jan - dec (1-12), but we want to create the sorting order that Jul - June (1-12).
Dual() - this function is used to create the months from 1 - 12, which will be used for sorting order.
Month(Makedate()) - this function is also create the month and year as the same field name.
where the month from the calendar table and sales table will be linked.
Peek() - It is one of the good function which will return the previous row field value, for example Stock(Jul) = 200,
when we use Peek(Stock) = Aug, this will be the July stock will be placed here.
Regards,
Prasath
What is Alt() ??
Is it for Alternate value???
Hi,
Alt() is the works as the switch case statments as in programming languages.
alt(case1[ , case2 , case3 , ...] , else)
The alt function returns the first of the parameters that has a valid number representation. If no such match is found, the last parameter will be returned. Any number of parameters can be used.
Example:
alt( date#( dat , 'YYYY/MM/DD' ),
date#( dat , 'MM/DD/YYYY' ),
date#( dat , 'MM/DD/YY' ),
'No valid date' )
Will test if the field date contains a date according to any of the three specified date formats. If so, it will return the original string and a valid number representation of a date. If no match is found, the text 'No valid date' will be returned (without any valid number representation).
Regards
Prasath
In order to solve my problem, we should first compute "ClosingSales"
then the ClosingSales for July will become the OpeningSales of Aug
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