Help for Task

[gaurav2017]

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

[#2]

Hi Gaurav,

give me some time i will come with the solution

Regards,

Prasath

[gaurav2017]

Hi All,

First Pls explain that code segments.

Regards

Gaurav Malhotra

[#4]

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

[gaurav2017]

What is Alt() ??

Is it for Alternate value???

[#6]

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

[gaurav2017]

In order to solve my problem, we should first compute "ClosingSales"

then the ClosingSales for July will become the OpeningSales of Aug

[gaurav2017]

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