OBIEE AGO function for 1 month prior

user4109719

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.

Gianni Ceresa

Hi,

Not sure what you use the TIMESTAMPADD... thing for, AGO doesn't require a date, it works based on the time of the "row".

AGO doesn't do any blackmagic, it's purely a SQL way to get data from the previous period based. It uses your data and model to get results.

If you model is wrong (or at least not fully correct) AGO could give you weird results just because it can't imagine the shortcut you maybe did in your RPD.

Look at the generated physical SQL, look at what logic it's applying and there you find what are the numbers it returns.

It will help you highlight RPD shortcuts or modelling shortcuts or "options" you adopted which prevent AGO to work directly and will require you some fixes first.

Christian Berg-0racle

@Gianni Ceresa I posted something similar last week but deleted it.

For the life of me I can't figure out why everybody insists on mindlessly writing code instead of reading about....two questions in here, seeing that code isn't the answer and trying to comprehend how the tool actually works. I had the impression we had explained that about 10 times this last quarter and about 20 times this year so far.

user4109719

I didn't explain things well.  The AGO function returns correct results when I use a single day: 

  

Date	Net Sales - Products and Services		Prior Net Sales - Products and Services
1/1/2017			0.00
1/3/2017	56730.21
1/4/2017	52892.96		56730.21
1/5/2017	44246.57		52892.96
1/6/2017	31256.83		44246.57
1/7/2017			31256.83
1/9/2017	55139.29
1/10/2017	60956.09		55139.29
1/11/2017	54924.51		60956.09
1/12/2017	58634.53		54924.51
1/13/2017	13765.49		58634.53
1/14/2017			13765.49
1/15/2017	0.00
1/16/2017	73381.44		0.00

The field that produced the date in the first column here is - Invoice Date"."Date - Invoice", which comes from a date dimension joined to the fact table.  The second column is the measure "Measures - Invoiced Sales"."Net Sales - Products and Services" from the fact table, and the third column is the AGO function: AGO("Measures - Invoiced Sales"."Net Sales - Products and Services",1).

All I am using the TIMESTAMPADD function for is to get the results by the first of the month.  The date dimension doesn't have a first of the month date stored in it.  I am not feeding it into the AGO function, it's to produce a column in the table.   When I replace  - Invoice Date"."Date - Invoice" with the TIMESTAMPADD function I get the first set of results in my original question, which obviously aren't correct.

Gianni Ceresa

Ok, in all your examples you posted the AGO function is used wrongly. The syntax is:

AGO(<measure_expression>, <level>, <number of period>)

You don't seem to have a <level> anywhere in what you posted so far.

If you want AGO to work at the month level, you need to tell the system to do so.

user4109719

I am trying to use the overloaded AGO function which only takes 2 inputs: 

there is a overloaded version of AGO:  AGO(expr, offset) which uses the grain of the Answers query as the level.

As I understand it, this is supposed to detect the grain from the field being used to generate the time period.  It works when using a single day.  I can't get it to work with a year either.  It produces blanks in the 1 year ago column.

Gianni Ceresa

user4109719 wrote:...supposed to detect the grain from the field being used ...

Every time you let the system do something supposing it works as designed, be ready for surprises (been there, done that, concluded it's good to suppose but it's safer to be sure ).

The grain of the analysis is generally identified based on the granularity of the time dimension columns you use. The problem is that often the grain is way more detailed than what you had in mind for various reasons.

So do you really need a single analysis jumping around on various levels? Or you look for a fixed level and in that case it's safer to set it in the function so the behaviour is constant and predictable?