HELP Reg Calculated Fields

Hi ,

I am having a pivot table with Columns Delta , and Demand .


I am trying to create a calculated field NetDemAvbl. based on this condition:

If my previous's week value of Delta is < 0 , NetDemAvbl = Demand - Delta

else NetDemAvbl = Demand .

My question is how do I extract last week's DELTA in Calculated field formula ?

Please give your suggestions.

Appreciate all your help.

Warm Regards,
Sunvin

Originally Posted by sunvin

...I am having a pivot table ...

I don't understand. Do you want to apply the calculation directly to the pivot table, or to the underlying table?

The format of the IF statement is:

=IF( <is this true or false> , <do this if true> , <otherwise do this> )

so I suppose you want this in the NetDemAvbl column:

=IF(Delta< 0, Demand - Delta, Demand)

Hi Buddy,

Yes, . I want to apply the calculation directly to the pivot table.

The Formula in this situation will be :

=IF((Delta) Prev_week < 0, Demand - (Delta)Prev_Week , Demand)

Can you please tell me how do arrive at (DELTA) previous week. and use it in the calculated_fields formula?

Thanks for your time,
warm regards,
Sunvin

I don't think you can modify a Pivot Table with the results of a calculation, so you will have to create a new column.

Can you post a sample workbook?

Please refer to the attached file 1.zip

Thanks,
Sunvin

Basically, NetDemAvbl will be a calculated field for which the formula will be

=IF((Delta) Prev_week < 0, Demand - (Delta)Prev_Week , Demand)

I guess (Delta)Prev_week will be another calculated field ...

any pointers on arriving at (Delta) prev_week will be appreciated !



Thanks and Regards,
Sunvin

Originally Posted by TheNorm

I don't think you can modify a Pivot Table with the results of a calculation, so you will have to create a new column.

Can you post a sample workbook?

HI Buddy, Any Luck ?

OK, it isn't pretty, but I think this is what you are after.

I've copied and pasted-special (values) from rows (4 to 9) to (15 to 20), then applied your IF statement at C22 and dragged it across.

Note that your IF subtracts a negative number, hence the results are all positive.

Hope that helps!