App Foundation Calender is not reading Universe Time Dim

Hi All,
I have to create metrics in APP Foundation.
I have a very simple Universe with a fact table and the Snapshot Table.
I tried to use self join in the Snap Shot Table and used the following code:

Monthly Data

to_Date(SNAPSHOT_DATE.SNAPSHOT_YR_MO) BETWEEN
ADD_MONTHS(@Prompt('BEGIN_DATE','D',,mono,free),-4)
AND @Prompt('END_DATE', 'D',,mono,free)

Example:
The time data is :YYYYMM:200403

I have a monthly calender in the App Foundation format: Sep 04

Measure Objects1:
Desc:
AGGR=SUM

Select:

Measure Object2:

Select

Basically it is a very simple universe and objects .
Results Iam Getting:
Month ID – Data
1 - - 10
2 - - 20
3 - - 30
Object1:
it takes only the first value for month(March) and ignores all the months and the result is a stright line.
Example:10

Object2:
It will give sum of all the values but as a single line for all the months.
Ex: 60

Can anyone help me what would be the problem.
Can i Use sysdate in the formula? If yes How should i use it in the universe and in the Application Foundation.

Regards,
Ganesh


Nimu (BOB member since 2004-04-27)

I am having alittle trouble following your issue, but from your code it looks like the End Date prompt should be @Prompt(‘END_DATE’,‘D’,mono,free). From what I understand AF does a Find and replace for the specific string… LEt me know if this is not the issue.


cduey :us: (BOB member since 2002-09-05)

Daisy,

We had a similar problem with a percentage column. What a consultant we brought in told us is that you have to sum/min/max/avg your objects IN ADDITION TO setting the AGGR=SUM/MIN/MAX/etc or the metrics won’t return the correct values.

For example - metric defined as “A/B” with AGGR=SUM; running the metric returned the sum of all the percentages (i.e. 375%) instead of “sum(a)/sum(b)”. Once we defined the object as “sum(A)/sum(B)” with AGGR=SUM, it returned exactly what it should have (i.e. 37.5%).

The reason this works and the first doesn’t is because the first returns one row per day in the where clause (30 days for June = 30 rows) but metrics can only handle ONE row per time period. The data must be summarized to return only one row per month in your scenario. A good way to test it is to run a full-client query and see if you only get one row. If you do, and the number is correct, the metrics will be correct as well.

Hope this helps.


SoxFanJB (BOB member since 2004-03-22)