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.
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.
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.