Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Time intelligence connection formula Expand / Collapse
Author
Message
Posted Thursday, October 20, 2011 10:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 13, 2014 1:43 AM
Points: 13, Visits: 96
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?
Post #1193877
Posted Friday, October 21, 2011 5:33 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 22, 2013 7:25 AM
Points: 347, Visits: 1,344
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
Post #1194280
Posted Friday, October 21, 2011 7:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 13, 2014 1:43 AM
Points: 13, Visits: 96
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.
Post #1194369
Posted Friday, October 21, 2011 7:39 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 22, 2013 7:25 AM
Points: 347, Visits: 1,344
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
Post #1194378
Posted Friday, October 21, 2011 9:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 13, 2014 1:43 AM
Points: 13, Visits: 96
I'm using PPoint 2010.
I created a Time Intelligence Post Formula Filters.
I hooked the filter to an analytic grid (source value="member UniqueName").
I wrote this connection formula:

yeartodate,year.firstmont:month


See the image to understand what i want



[url=https://docs.google.com/leaf?id=0B8U0FOJvinvSY2JlM2Q5YmQtMDNkNi00OTQ2LWJiMDAtOGZhZWFhN2RlZjI3&hl=en_US][/url]
Post #1194505
Posted Friday, October 21, 2011 10:11 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 22, 2013 7:25 AM
Points: 347, Visits: 1,344
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
Post #1194537
Posted Monday, October 24, 2011 1:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 13, 2014 1:43 AM
Points: 13, Visits: 96
[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;
Post #1195048
Posted Monday, October 24, 2011 9:15 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 22, 2013 7:25 AM
Points: 347, Visits: 1,344
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
Post #1195294
Posted Monday, October 24, 2011 9:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 13, 2014 1:43 AM
Points: 13, Visits: 96
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
Post #1195328
Posted Monday, October 24, 2011 10:10 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 22, 2013 7:25 AM
Points: 347, Visits: 1,344
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
Post #1195345
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse