Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi all,
Could anyone help in above calculation?
Hi karthi,
Check date format
See attached qvw.
its working perfectly..:)
Thank you very much