5 Replies Latest reply on Oct 31, 2016 7:06 AM by Norbert Maijoor

# How to capture previous value if current value is NULL

Hi Guys,

Hope someone can assist me on a calculated field. This is actually a predictive task, where i would like to know my current

DateTimeapi_main (current)p_api_main
30/10/2016 9:00NULL48
30/10/2016 8:005050
30/10/2016 7:004545

Problem 1:

As per above table,my api_main have yet to get the actual result for 30/10/2016 9:00, so i would like to create a calc field; if date time selection is 30/10/2016 9:00 and api_main is null, then select date time 30/10/2016 8:00 and api_main = 50

Time = -1/24 (Date time 30/10/2016 9:00 selection)

is it possible?

Problem 2:

If date time selection is = 30/10/2016 8:00, date will display current date and value :api_main value is not null.

Date selection: 30/10/2016 8:00 and display api_main = 50 **did not have time = -1/24

This is the formula i hope:

if [api_main] <0 then ((MAX([Datetime Predicted])) -1/24)

END

Thank you,

Regards,

Stan Lee

• ###### 1. Re: How to capture previous value if current value is NULL

Hi Stan,

Find my approach as reference below and stored in attached workbook version 9.3

1.calculated field "IFNULL api_main": IFNULL([api_main],0)

2. calculated field: "Value maxdate"     if max([Datetime])=attr([Datetime]) then sum([IFNULL api_main]) END

3. Previous value:

if [Value maxdate]=0 and ATTR([Datetime])=max([Datetime]) then PREVIOUS_VALUE(-1)

ELSEIF  [Value maxdate]>0 and ATTR([Datetime])=max([Datetime]) then sum([api_main]) END

For sure not a "rock solid" end solution but may be to get you started

• ###### 2. Re: How to capture previous value if current value is NULL

Hi Sir,

Thanks for your help, finally i get it out... however, there is some issue as my boss changes the requirement a bit...

now, rather than having more than +1 hour gap, there is +24 hours gap.

How do i get the API current 31/10/2016 = 45

and Min API and Max API at 12 and 45

 API Current Min API Max API 31/10/2016 6:00 42 30 54 31/10/2016 7:00 40 28 52 31/10/2016 8:00 45 33 57 31/10/2016 9:00 0 12 45 31/10/2016 10:00 0 25 55 31/10/2016 11:00 0 38 65 31/10/2016 12:00 0 13 19 31/10/2016 13:00 0 16 21 31/10/2016 14:00 0 19 24 31/10/2016 15:00 0 23 26 31/10/2016 16:00 0 26 29 31/10/2016 17:00 0 29 31 31/10/2016 18:00 0 32 34 31/10/2016 19:00 0 36 36 31/10/2016 20:00 0 39 39 31/10/2016 21:00 0 42 41 31/10/2016 22:00 0 45 44 31/10/2016 23:00 0 49 46 1/11/2016 0:00 0 52 49 1/11/2016 1:00 0 55 51 1/11/2016 2:00 0 58 54 1/11/2016 3:00 0 62 56 1/11/2016 4:00 0 65 59 1/11/2016 5:00 0 68 61 1/11/2016 6:00 0 71 64 1/11/2016 7:00 0 75 66

Really thanks a lot...

Regards,

Stan Lee

• ###### 3. Re: How to capture previous value if current value is NULL

Hi Stan,

Glad i could help out. Thanks for the "awards". Much appreciated:)

• ###### 4. Re: How to capture previous value if current value is NULL

Hi Sir,

Actually got another issue i got.. hope u get your assistant..

Thank you,

Regards,

Stan Lee

On Mon, Oct 31, 2016 at 9:37 PM, Norbert Maijoor <

• ###### 5. Re: How to capture previous value if current value is NULL

Hi Stan,

Find my approach based on LOD expression as reference below and stored in attached workbook version 9.3

Not sure if I understand correct. You "bolded" 45 where 65 is the max for the day.