Time intelligence connection formula
-
Hi,
I have create a BI time connection formula filter and I have hooked a grid to it.
In the grid I have a calendar hierachy on columns. The grid show average for year level.
In the connection formula i wrote:
"year-1, year-2,year,year.firstmonth:month"
When i select, for example, '2011-04-30' in the BI filter, i would like to see the average value for 2010, average value for 2011 from january to april and the value on month level, but for 2011 I see the average value for all month (not only from jan to april).
How can i do this?
-
Have you tried using the yeartodate reference instead of the year? This should provide the value you are looking for, but the display name that you will get might not be what you would like to see.
Here is a good reference for TI functions:
PerformancePoint Time Intelligence quick reference
http://technet.microsoft.com/en-us/library/ff701696.aspx
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com -
Hi, thank for repling.
I've tried yeartodate function, but it give me the sum of the month even if in the cube i have a mdx scope that calculated the average for year level.
-
yeartodate gives you the sum of month? not sure if I am following this along with what else you might have setup in the cube that you are referring to.
could you provide some examples of what you are currently using, what you are seeing as far as numbers, and what you would like to see. the yeartodate should provide you the value you are looking for based on your selection in the TI filter. you are using PPS 2010, right?
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com -
Can you provide the information for the measure you are referencing? What aggregation is it using? If it is an Average calculation then you will also see the Avg with the YearToDate reference as well. Are you using a calculated measure to get at the Average value for the month? It will roll up properly if you are.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com -
[Measures].[Values] properties:
AggregateFunction:SUM
DataType:Double
FormatString:#,##0.00
I wrote an mdx script to overwrite measure value on year level:
SCOPE ([Measures].[Values]);
this=
(case when
[Calendar].[Year - month - day].level.name='year'
then
AVG( [Calendar].[Year - month - day].currentmember.children,[Measures].[Values])
else
([Dim Performance].[Performance].currentmember,[Measures].[Values])
end);
End scope;
-
Just want to verify and see if this is working for you or if you still need additional assistance on getting this setup and working. Is the SCOPE statement working as expected for you?
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com -
In BusinessIntelligence development studio and in excel the scope statement work fine.
It work fine in PPoint without any filter, but when I add a post formula filter and I put in connection formula "yeartodate", I don't see average at year level but sum
-
That is because the level is not correct when using yeartodate, it just isn't simply the year and most likely using a ytd or periodstodate MDX function (you could verify by using the Profiler trace to see what gets executed, not sure off the top of my head).
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com -
Sorry, I didn't saw your previous reply when I public my last post.
So I have to use an MDX function like periodsToDate in my connection formula? Is it possible using a BI time intelligence post formula filter?
what is Profiler trace?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply