en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Maurice Reid  
#1 Posted : Friday, January 30, 2015 6:26:29 PM(UTC)
Maurice Reid
Rank: Advanced Member

Groups: extranet\Forum
Joined: 5/27/2013(UTC)
Posts: 11

Hello All,

I have an analysis which shows a list of companies grouped by an Accounts comparison on the vertical axis, and the current year’s months and Year to Date totals on the horizontal axis.

What I would like to do is have a YTD calculation that totals as it should from Jan to current date for some Account comparisons, but for others it would just display the current month’s value.

So for example, let’s say that we are in the month of November, the YTD Actual value for Expenses would be calculated as the sum of all Actual Expenses from January to November. But if the comparison were Total Gross Inventory the YTD Actual would be calculated as just the Actual value for November.

Is it possible to create a calculation to do this in Targit?

Layout

Thanks in advance.
Páll Bjarkason  
#2 Posted : Monday, February 2, 2015 2:46:48 PM(UTC)
Páll Bjarkason
Rank: Advanced Member

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

In scenario A the Inventory is implemented in the measure, and then you are home free 
In scenario B you have to implement the Inventory logic in TARGIT. It is most likely possible to make a custom calculation which can handle this.
I assume that you use the crosstab depicted in the post and you got a row named “Total Gross Inventory”.
The then you can check for the row “Total Gross Inventory” and make the desired calculation.
Is this the solution you are looking for?
Best regards

Páll, CTS
Maurice Reid  
#3 Posted : Monday, February 2, 2015 7:50:39 PM(UTC)
Maurice Reid
Rank: Advanced Member

Groups: extranet\Forum
Joined: 5/27/2013(UTC)
Posts: 11

Thanks for the response Páll.

Total Gross Inventory is one of the elements of a comparison named Accounts(the calculation for it is done in the cube). I would like to create a YTD calculation that would sum the month values from Jan to current date for all other elements of the Accounts Comparison but if the comparison elements is Total Gross Inventory then the YTD value would equal the current month value(instead of the sum from Jan to current month).

The screenshot gives an example of the layout of the Accounts comparison with Total Gross Inventory, the month values and where i would like the YTD calculation to be.

If this could be done in Targit I would appreciate seeing how the code for the YTD calculation would look.

Thanks again.
Páll Bjarkason  
#4 Posted : Tuesday, February 3, 2015 12:40:46 PM(UTC)
Páll Bjarkason
Rank: Advanced Member

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

I propose to make new measures via Custom calculation -> as a new measure
YTD returns the year to date, from left to right: YTD sum(d1:0,0,m1)
Actual (this month) returns this month’s value: Actual (this month) sum(0, 0, m1)

It is possible to put both calculations in the same column and then show YTD or Actual (this month) depending a check of the presence of “Total Gross Inventory” row.
The goal of the temp_TGI column is to return 1 for Total Gross Inventory and 0 for the rest.
The condition syntax could be something like:
If there were one dimension on the y axis, this calculation would work:
if allcount(d1, d1:0, m1) = allcount(d1, d-1:@"[ Total Gross Inventory]", m1) then sum(0, 0, m1) else sum(d1:0,0,m1)
In your example there seem to be two nested dimensions on the y axis, this calculation would work:
if allcount(d1, d1:0(l(1,0)), m1) = allcount(d1, d1:@"[ Total Gross Inventory]"(l(1,0)), m1) then 1 else 0

UserPostedImage

The L (level) parameter references the most left column
The 1-0 switch can be incorporated into an if-statement returning YTD or Actual (this month).
It can be done like this, replacing 1 and 0 whit the YTD or Actual (this month) syntax above.

UserPostedImage

Now you can check the “YTD and Actual” column in February and compare Profit and Total Gross Inventory rows.

Then you can delete all except the “YTD and Actual” custom calculation.

Edited by user Wednesday, February 4, 2015 8:58:53 AM(UTC)  | Reason: Not specified

Best regards

Páll, CTS
Maurice Reid  
#5 Posted : Tuesday, February 3, 2015 7:56:20 PM(UTC)
Maurice Reid
Rank: Advanced Member

Groups: extranet\Forum
Joined: 5/27/2013(UTC)
Posts: 11

Thanks a lot Páll. This solved the problem :)
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