if missing value exists get previous value

[QSense]

Hello,

I have a table that some year month's values are missing. On hand = previous on hand + input - output 

but if there is missing value on hand value is calculated wrongly. First table calculation is wrong.

Second table on hand calculation is true. 

since in 202006 previous on hand (30) input (1) - output (2) = 29

Please help.

ID	Y M	input	output	on hand
1	202002	5	8	30
1	202003	-	-	-
1	202004	-	-	-
1	202005	-	-	-
1	202006	1	2	-1
1	202007	10	9	0
1	202008	3	4	-1

ID	Y M	input	output	on hand
1	202002	5	8	30
1	202003	-	-	-
1	202004	-	-	-
1	202005	-	-	-
1	202006	1	2	29
1	202007	10	9	30
1	202008	3	4	29

[Kashyap_R]

Hi

Try using Peek() or  Previous function.

Thanks

[QSense]

hello ,

It does not work . I tried.

[lorenzoconforti]

Peek works for me; see attached example with your data

[QSense]

thanks for your reply,

in data missing values are "-" instead of "". when I changed  into "-" , ıt didint work.

[lorenzoconforti]

see attached

[QSense]

thanks very much for fast reply. 

actually in my data missing values are not present . I mean , original data is 

ID	Y M	input	output	on hand
1	202002	5	8	30
1	202006	1	2	29
1	202007	10	9	30
1	202008	3	4	29

[lorenzoconforti]

Then I don't understand the problem. How do you fill in the gaps in "Y M"?

[QSense]

Let me aunderstand. 

Actually for each id I have 12 months, but some id's months are missing like example. there is no problem if there is all months. In missing months Problem occurs.  In below example, in 202006 on hand value must be 29 not -1.

ID	Y M	input	output	on hand
1	202002	5	8	30
1	202006	1	2	29
1	202007	10	9	30
1	202008	3	4	29

[lorenzoconforti]

A couple of questions:

- do you add the missing month in another table (e.g. calendar) or they are coming from other ID's?

- it's your plan to fill the missing months from a script perspective (i.e. generating the missing months and filling them in with the previous on hand value) or just in a chart (i.e. only when displaying the information)?