en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Line Jepsen  
#1 Posted : Friday, November 24, 2017 7:20:15 AM(UTC)
Line Jepsen
Rank: Newbie

Groups: extranet\Forum
Joined: 10/16/2017(UTC)
Posts: 3

Is there a way of making a calculation fixed?
I have a calculation defining what the total sales for e.g. Q1 is.
This calculation is just April's sales and outstanding amount + May's sales and outstanding amount + June's sales and outstanding amount = total sales for Q1, my calculation looks like this: sum(d-12, 0, m1) + sum(d-12, 0, m2) + sum(d-11, 0, m1) + sum(d-11, 0, m2) + sum(d-10, 0, m1) + sum(d-10, 0, m2)
But this calculation only works when I am drilled down on months, but if I drill out to quarters only, my calculation is no longer working.
So my question is, can I make my calculation more fixed, saying more like "this exact field" everytime, instead of sum(d-12, 0 ,m1) - because this field will change when periods are changed.
Ole  
#2 Posted : Friday, November 24, 2017 7:43:54 AM(UTC)
Ole
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 397

Hi Line,

Yes. You can make it fixed in this way - e.g. if you want a calculation to always hit May of 2017:

sum(@"[2017].[Q2].[May]", 0, m1)

In this example I am assuming you have a Year-Quarter-Month hierarchy.

Please note that this syntax requires that your hierarchy is expanded to an exact level, in this case the month level. If you expand it to the date level or collapse it to the quarter level, it will not work.

BR / Ole
I work as an employee with TARGIT A/S. Any statement made by me in this forum represents my own opinion and state of mind, and is not necessarily related to or representative of TARGIT A/S.

UserPostedImage Ole Dyring
Line Jepsen  
#3 Posted : Friday, November 24, 2017 8:25:53 AM(UTC)
Line Jepsen
Rank: Newbie

Groups: extranet\Forum
Joined: 10/16/2017(UTC)
Posts: 3

Thank you.

Yes exactly, year, quarter, month hierachy.
So I cannot make a calculation with the ability of expanding to other date levels?

Ole  
#4 Posted : Monday, December 4, 2017 9:02:02 AM(UTC)
Ole
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 397

You can set up your calculation like this:

sum(@"[2017].[Q2]"(l2), 0, m1)

Notice that (l2) is a small "L", and refers to level 2 of your hierarchy.

With this example, you can expand your data to the Month or Day level and still get calculated data for 2017/Q2.

However, you cannot collapse to the Year level as this will totally exclude the Quarter data.

BR / Ole
I work as an employee with TARGIT A/S. Any statement made by me in this forum represents my own opinion and state of mind, and is not necessarily related to or representative of TARGIT A/S.

UserPostedImage Ole Dyring
Users browsing this topic
Anonymous
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Notification

Icon
Error