Computed Field - Date

[Anonymous]

Looking for help to create a computed field to calculate Expiry date.

For example –

Start Date: 15/11/2017

Validity Period (Months): 12, 24, 36, 48, 60 (Pick List)

Expiry Date: Computed Field

Many Thanks.

[owais.zahid]

Thanks for the post.

You can use the following computed field formula to calculate expiry date:

STARTDATE + SHELFLIFE * interval '1 month'

Where, StartDate is the date field and SHELFLIFE is the picklist of number of months. 

Thanks

Owais

[Anonymous]

Thanks for the response. Something is wrong... it is not working. Please see screenshots attached.

Very much appreciate your assistance.

Regards,

Venkata

P.S. I have changed the field type to integer and entered the value (e.g. 01, 12 , 24), it worked. Problem is only with pick-list.

.

[owais.zahid]

Thanks Venkata,

For this formula to work, the cycle_month should have an integer value. If your picklist value is string, then you should try something like:

LAST + to_number(CYCLE_MONTHS) * interval '1 month'

-Owais

[Anonymous]

Dear Owais,

Thanks for your response. 

Field "CYCLE_MONTHS" is a pick-list with integers only (see image 05 attached)... unfortunately LAST + CYCLE_MONTHS * interval '1 month'   is didn't work.

When I have changed the computed field formulae to      LAST + to_number(CYCLE_MONTHS) * interval '1 month'    I am getting an error message(s) (see images 03, 04 attached).

Very much appreciate your assistance.

Kind Regards,

Venkata Kancharla

[owais.zahid]

Hi Ventaka,

Thanks for the details. 

If your picklist values are integers, then you don't need to use to_number function (as it will throw an error). 

The formula is working for me, the only difference that I can spot is that the type of your computed field is 'Date'. 

Following are some of the suggestions that you can try:

- Change your computed field data type to 'Single Line Text'.

- Try adding parentheses to ensure correct order to operations like

   LAST + (CYCLE_MONTH * interval '1 month')

- Make sure that the error is not happening because one of the values in the formula is not available or undefined. In that case, you need to add IF condition to ensure that this formula only applies when the user has selected valid LAST and CYCLE_MONTH value.

Regards

Owais

[Andrew.Shaw · Accepted solution]

Hello Venkata,

Owais is correct in saying that the formula should be: LAST_DATE + (CYCLE_MONTHS * INTERVAL '1 month')

However, since it's a Listbox, you will get the index of the entry, not the selected value. 

One way to circumvent this is to find the index value of each entry and then write a formula like this:

LAST_DATE + (

(CASE WHEN CYCLE_MONTHS = 1 THEN 2
WHEN CYCLE_MONTHS = 2 THEN 1
WHEN CYCLE_MONTHS = 3 THEN 5
WHEN CYCLE_MONTHS = 4 THEN 4
WHEN CYCLE_MONTHS = 5 THEN 3
WHEN CYCLE_MONTHS = 6 THEN 6
ELSE 0
END)

* INTERVAL '1 month')

In this case, we test the index value, i.e. CYCLE_MONTHS = 5 and we use the entry value that was selected by the user in the picklist, i.e. 3. 

Beware, the index values change for each picklist so you will have to check your picklist for each entry.

It's not very elegant but if you don't have that many entries, it works a treat.

Hope this helps.

[tony.mandatori]

You can simplify this by using a referenced workspace and a derived attribute:

START_DATE + DERIVED_MONTHS*INTERVAL '1 months'

Validity in this case is workspace picklist

DERIVED_MONTHS is the derived attribute that points to the VALIDITY workspace attribute.

8805 in the diagram above is the DMSID of the 12 months item in the Validity workspace... a little contrived. But i didn't like the possible mismatch with index and the number of months.

[Anonymous]

Thank you for suggesting an alternate solution.

[Anonymous]

Hello Andrew,

Solution suggested worked...

Many Thanks,

Venkata