en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Martin Hobér  
#1 Posted : Monday, October 21, 2013 10:55:16 AM(UTC)
Martin Hobér
Rank: Newbie

Groups: extranet\Forum
Joined: 10/21/2013(UTC)
Posts: 4

This is most likely an easy problem, I however haven't found a solution myself.
How do I set up a dynamic calculation of variances meaning I would like the following:

I have a table where I have selected every month from December 2012 until september 2013 by making a dynamic selection (current month minus one).
In this table I would like to fix my base month to December 2012 and calculate the difference/variance between my base (being December 2012) the current month minus 1 (being September 2013 in this case). Once we have the month of november I would like to calculate the variance between my base (still being December 2012) and my new current months -1 (being October 2013).
Is there anything like $$ to fix column references like in Excel ?

Best regards

Martin Hobér
Dansk Standard
Niels Thomsen  
#2 Posted : Monday, October 21, 2013 11:49:11 AM(UTC)
Niels Thomsen
Rank: Advanced Member

Groups: extranet\Forum
Joined: 4/5/2011(UTC)
Posts: 103

Hi Martin

I'm assuming that your period is on the vertical axis of your crosstab.
With that in mind I would choose to make a calculated row and the correct syntax could be sum(0,d-1,m1)-sum(0,d1,m1)
In the syntax you allways refer to the column first (in this case 0 meaning current column), the row secondly (d-1 means first row from the bottom and d1 means fist row from the top), and thirdly the measure (I'm assuming you only have one measure in the crosstab so that would be m1).

I strongly recommend the TARGIT advanced calculations training for this sort of issue.

Hope it makes sense.
Hope it makes sense.

Best Regards
Niels Thomsen

https://dk.linkedin.com/in/ncthomsen


Martin Hobér  
#3 Posted : Monday, October 21, 2013 1:10:05 PM(UTC)
Martin Hobér
Rank: Newbie

Groups: extranet\Forum
Joined: 10/21/2013(UTC)
Posts: 4

Hello again,

Niels, thank you for the fast reply and the description on how formulas are defined - this subject has been on my mind for quite some time :-).
As to the problem at hand, my table is as following:
When I define my formula for the variance between december and september I get the following: sum(d-10,0,m1)-sum(d-1,0,m1) where I assume the d-10 is referring to the current column minus 10 (which is december). Once the month turns to November, my variance column will still be sum(d-10,0,m1)-sum(d-1,0,m1), and the d-10 is now refering to the month January - the correct reference for the formula should be d-11.
How do I define my variance formula that for any given month return my "current month minus 1" subtracted december - hence a dynamic formula with a fixed reference ?

MY TABLE:
Group December Januar Februar Marts April Maj Juni Juli August September Variance

A 5 7
B 6 10
C 5 8
D 1 3
E 1 6

(the example numbers should be for december and september respectivly)

And as for the advanced training, I will take it up with my boss :-)


Best regards

Martin

Edited by user Monday, October 21, 2013 1:11:28 PM(UTC)  | Reason: Not specified

Páll Bjarkason  
#4 Posted : Monday, October 21, 2013 2:29:50 PM(UTC)
Páll Bjarkason
Rank: Advanced Member

Groups: extranet\Forum
Joined: 1/13/2012(UTC)
Posts: 156

Maybe the solution is to use the correct dynamic period, and then the syntax Niels suggests.

Date From:
Specific: Year 2012
Specific: Month December

Date To:
Relative: Month -1

PS If you want to use Niels’s calculation, make sure to move the period to the y-axis/primary axis/vertical axis

Edited by user Tuesday, October 22, 2013 8:20:58 AM(UTC)  | Reason: Not specified

Best regards

Páll, CTS
Niels Thomsen  
#5 Posted : Monday, October 21, 2013 3:57:32 PM(UTC)
Niels Thomsen
Rank: Advanced Member

Groups: extranet\Forum
Joined: 4/5/2011(UTC)
Posts: 103

Hi again Martin

It's ok to have the months on the horizontal axis.

If December is allways the first column to the left in your crosstab you should refer to it as column d1. When we are talking about columns they can either be referenced from the left - d1, d2, d3 etc. or from the right d-1, d-2, d-3 etc.

So the d-10 means column no ten counting from the right and will not work for your purpose. I would simply replace d-10 with d1 which is the first column counting from the left.

If December depending on filtering might suddenly be no 2 or 3 from the left you can actually lock the reference much like the $-signs in excel.
The actual syntax for that would in your case be something like: sum(d-1,0,m1)-sum(@"[December]",0,m1).

Looking very much forward to seeing you on advanced calculations ;-)
Hope it makes sense.

Best Regards
Niels Thomsen

https://dk.linkedin.com/in/ncthomsen


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