Need help in writing the expression to obtain the desired values

[Anonymous]

Hi Team,

Using the below data, need to achieve the calculation of "3 days back Country_val"  in a straight table(the current result which i m getting for the calculation and  the expected result are shown in the below second table )                                                                                                                                                                                                                                                                               

Country	Date	Country_val
ABC	3/3/2015	75
ABC	3/2/2015	45
ABC	27/2/2015	34
ABC	26/2/2015	56
ABC	25/2/2015	64
ABC	24/2/2015	45
ABC	23/2/2015	74
ABC	20/2/2015	23
ABC	19/2/2015	120
ABC	18/2/2015	56
DEF	3/3/2015	54
DEF	3/2/2015	54
DEF	27/2/2015	45
DEF	26/2/2015	89
DEF	25/2/2015	96
DEF	24/2/2015	101
DEF	23/2/2015	154
DEF	20/2/2015	48
DEF	19/2/2015	425
DEF	18/2/2015	45
XYZ	3/3/2015	87
XYZ	3/2/2015	48
XYZ	27/2/2015	44
XYZ	26/2/2015	45
XYZ	25/2/2015	185
XYZ	24/2/2015	154
XYZ	23/2/2015	158
XYZ	20/2/2015	95
XYZ	19/2/2015	45
XYZ	18/2/2015	42

Logic for "3 days back Country_Val" is

for each country, for each date-> we need to get the previous 3rd working day Country_Val value.

Current Problem:

Country=ABC, Date= 26/2/2015, 3 days back Country_Val should return 74(i.e Country Val of 23/2/2015) but it is returning null since the data for 23/2/2015 is not present in the straight table.

Note: no of days back keeps on changing based on the user preference(here it is 3), so the calculation for it should be done in the straight table itself.                                                                                                                                                                                                                                                                  

Country	Date	Country_val	3 days back Country_Val(Current   Result)	3 days back Country_Val(Expected   Result)
ABC	3/3/2015	75	56	56
ABC	3/2/2015	45	64	64
ABC	27/2/2015	34	45	45
ABC	26/2/2015	56	-	74
ABC	25/2/2015	64	-	23
ABC	24/2/2015	45	-	120
DEF	3/3/2015	54	89	89
DEF	3/2/2015	54	96	96
DEF	27/2/2015	45	101	101
DEF	26/2/2015	89	-	154
DEF	25/2/2015	96	-	48
DEF	24/2/2015	101	-	425
XYZ	3/3/2015	87	45	45
XYZ	3/2/2015	48	185	185
XYZ	27/2/2015	44	154	154
XYZ	26/2/2015	45	-	158
XYZ	25/2/2015	185	-	95
XYZ	24/2/2015	154	-	45

Could anyone help in doing this?

Thanks in  advance

[Anonymous]

Hi all,

Could anyone help in above calculation?

[#3]

Hi karthi,

Check date format

[Gysbert_Wassenaar · Accepted solution]

See attached qvw.

[Anonymous]

its working perfectly..:)

Thank you very much