In OBIEE, I am trying to get a measure for the previous and current months on the same line. I'm using the AGO function to get the previous month measure. It works fine if I use a single day as the grain but the monthly results don't make any sense. I'm using this expression to get the month:
TIMESTAMPADD(SQL_TSI_DAY, -1*(DAYOFMONTH("- Invoice Date"."Date - Invoice" )-1) , "- Invoice Date"."Date - Invoice" )
This is to get the Prior Month Sales
AGO("Measures - Invoiced Sales"."Net Sales - Products and Services",1)
These are the results starting 1/1/2017. The results are not even close to correct. I can't figure out what it's doing. For 4/1/2017 the values are identical for some reason.
| Month | Net Sales - Products and Services | Prior Net Sales - Products and Services |
| 1/1/2017 | 241584.80 | 224838.84 |
| 2/1/2017 | 219997.29 | 16745.96 |
| 3/1/2017 | 268049.85 | 251091.32 |
| 4/1/2017 | 14951961.73 | 14951961.73 |
| 5/1/2017 | 17301172.58 | 16255156.18 |
| 6/1/2017 | 16900342.80 | 1046016.40 |
| 7/1/2017 | 15655569.48 | 702392.04 |
| 8/1/2017 | 16997174.82 | 832775.94 |
| 9/1/2017 | 17516351.28 | 17516342.38 |
| 10/1/2017 | 18943948.55 | 8.90 |
| 11/1/2017 | 18466469.43 | 1001084.96 |
| 12/1/2017 | 17601620.02 | 17600106.96 |
| 1/1/2018 | 19183573.22 | 18258195.81 |
| 2/1/2018 | 16925629.50 | 15949988.17 |
| 3/1/2018 | 18051468.24 | 975641.33 |
| 4/1/2018 | 17696257.13 | 16842232.22 |
| 5/1/2018 | 18674737.59 | 17818018.33 |
| 6/1/2018 | 17415379.80 | 17414595.93 |
| 7/1/2018 | 18210969.05 | 17243750.77 |
| 8/1/2018 | 19705728.75 | 18896268.76 |
| 9/1/2018 | 16641547.06 | 809459.99 |
| 10/1/2018 | 19764379.07 | 18903093.53 |
| 11/1/2018 | 17758578.86 | 17043570.38 |
| 12/1/2018 | 17457314.21 | 16468831.00 |
| 1/1/2019 | 18992934.93 | 18275014.95 |
| 2/1/2019 | 16674484.18 | 717919.98 |
| 3/1/2019 | 17713099.27 | 17710879.05 |
| 4/1/2019 | 19152519.32 | 2220.22 |
| 5/1/2019 | 13781477.59 | 1023824.42 |
Thanks.