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

[gaurav2017]

Does Nobody knows what am i talking

[gaurav2017]

Please Help..........I am in great problem.

[jonathandienst]

Gaurav

Please see the attached. I have updated the load script and added a new Stock Report table to the Main tab. Is this what you need?

Regards

Jonathan

[gaurav2017]

Hi Jonathan,

My company is not having spare CALs, therefore I am using Personal Edition, so pls share the code

[#6]

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';

Calendar:

Load Dual(Month, RowNo()) As Month, Quarter Inline

[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];

Sales:

Load *, Month(MakeDate(FYear, Period)) As Month Inline

[A, B, C, D, E, F, G, Period, FYear, PrimarySales, SecondarySales

P, Q, R, S, PQ, RST, 1, 07, 2012, 500, 25

P, Q, R, S, QP, TSR, 1, 08, 2012, 100, 30

P, Q, R, S, PP, RRS, 1, 09, 2012, 130, 40

P, Q, R, S, QQ, SST, 1, 10, 2012, 80, 10];

Join(Sales)

LOAD Month,

    FYear,

    Alt(Peek('ClosingBalance'), 0) As OpeningBalance,

    //Peek('ClosingBalance') + Sum(PrimarySales) - Sum(SecondarySales) As ClosingBalance

    Alt(Peek('ClosingBalance'), 0) + Sum(PrimarySales)  - Sum(SecondarySales) As ClosingBalance

Resident Sales

Group By FYear, Month

Order By FYear, Month;

[gaurav2017]

Please Explain the code segments which are color coded:

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';

Calendar:

Load Dual(Month, RowNo()) As Month, Quarter Inline

[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];

Sales:

Load *, Month(MakeDate(FYear, Period)) As Month Inline

[A, B, C, D, E, F, G, Period, FYear, PrimarySales, SecondarySales

P, Q, R, S, PQ, RST, 1, 07, 2012, 500, 25

P, Q, R, S, QP, TSR, 1, 08, 2012, 100, 30

P, Q, R, S, PP, RRS, 1, 09, 2012, 130, 40

P, Q, R, S, QQ, SST, 1, 10, 2012, 80, 10];

Join(Sales)

LOAD Month,

    FYear,

   Alt(Peek('ClosingBalance'), 0) As OpeningBalance,

    //Peek('ClosingBalance') + Sum(PrimarySales) - Sum(SecondarySales) As ClosingBalance

    Alt(Peek('ClosingBalance'), 0) + Sum(PrimarySales)  - Sum(SecondarySales) As ClosingBalance

Resident Sales

Group By FYear, Month

Order By FYear, Month;

[gaurav2017]

A new situation is there now. I have opening Sales for July, 2012.. Now the

"ClosingSales of July" = "OpeningSales of Aug"

"ClosingSales of Aug" = "OpeningSales of Sep"

& so on.

Plus I need

ClosingSales = (OpeningSales+PrimarySales) - SecondarySales

Regards,

Gaurav Malhotra

[jonathandienst]

I will leave that to Prasath...

[gaurav2017]

Jonathan, pls help.