Calculate a Field in script based on other calculated Fields

[Pestaninha]

Hi  - I need to Calculate a Field based on previous fields I calculated in the script

I basically need to calculate [Net Available Stock]  based on [Stock IN], [Stock] and [Demand requested ] Calculated previously in the script.

Please see code sample bellow. if someone could help would be great. Many thanks 

NoConcatenate
Metrics:
Load
SuperKeyM,
DATA,
QTY,
DateKey as Week,
[Weekly Avarage],
Period
Resident MasterData;

//// Calculate supply In ////

Left Join(Metrics)
Load
SuperKeyM,
sum(QTY) as [Supply In]
Resident Metrics
Where DATA='SI'
Group by SuperKeyM;

////Calculate Stock ////

Left Join(Metrics)
Load
SuperKeyM,
sum(QTY) as [Stock]
Resident Metrics
Where DATA='ST'
Group by SuperKeyM ;

//// Calculate Demand past////
Left Join(Metrics)
Load
SuperKeyM,
sum(QTY) as [Demand Requested1]

Resident Metrics
Where Period<>'Future' and DATA='TD'
Group by SuperKeyM;

//// Calculate Demand Future////

Left Join(Metrics)
Load
SuperKeyM,
sum(QTY) as [Demand Requestedcalc2]

Resident Metrics
Where Period='Future' and DATA='TD'
Group by SuperKeyM;

/// Calculate Compare Demand With Avarage////

Left Join(Metrics)
Load
SuperKeyM,
If([Demand Requestedcalc2]>[Weekly Avarage],[Demand Requestedcalc2],[Weekly Avarage]) as [Demand Requested2]
Resident Metrics
Where Period='Future';

///////////////////////////////////////Doesn't work from here //////////////////////////////////////////////////////////

/// Calculate Total Demand requested]

Left Join(Metrics)
Load
SuperKeyM,
[Demand Requested1] + [Demand Requested2] as [Demand requested]
Resident Metrics;

/////Calculate Net Available Stock /////

NoConcatenate
MetricsFinal:
Load
SuperKeyM,
[Supply In],
[Stock],
Week,
[Demand requested],
If(Week='W-13',(([Stock]+ [Supply In]) - ([Demand requested])), (((Peek([Net Available Stock])) +[Supply In])- ([Demand requested]))) as [Net Available Stock]
Resident Metrics;
Drop Table Metrics;

[Pestaninha]

Hi Sunny -  I still have an issue with this, the table is duplicating values, I need a value by SuperKeyM otherwise calculations are incorrect. Any ides how I do this?

Many Thanks

[sunny_talwar]

Would you be able to share an example?

[qliktowin]

Hi Sunny,

I have a similar case want to know which one would be best solution. Calculating the field again by doing a resident load or calculating it while creating a chart.

[sunny_talwar]

It depends....in most cases, I would say that it would be better to do it in the script... but this is not always 100% true. If your data model gets really complicated and huge, it might be better to do it on the front end.