 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.
|
|
|
|
|
|
 Rank: Administration
Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 460
|
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.  Ole Dyring |
|
|
|
|
|
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?
|
|
|
|
|
|
Rank: Administration
Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 460
|
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. Ole Dyring |
|
|
|
|
|
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.