Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Time intelligence connection formula


Time intelligence connection formula

Author
Message
stefani.giorgio
stefani.giorgio
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 114
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?
denglishbi
denglishbi
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 1362
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
stefani.giorgio
stefani.giorgio
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 114
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.
denglishbi
denglishbi
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 1362
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
stefani.giorgio
stefani.giorgio
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 114
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://www.sqlservercentral.com/Forums/Uploads/image-unavailable.png][/url]
denglishbi
denglishbi
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 1362
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
stefani.giorgio
stefani.giorgio
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 114
[Measures].[Values] properties:
AggregateFunction:SUM
DataTypeBigGrinouble
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;

denglishbi
denglishbi
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 1362
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
stefani.giorgio
stefani.giorgio
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 114
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
denglishbi
denglishbi
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 1362
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search