en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Steffen Eriksen  
#1 Posted : Thursday, October 25, 2012 5:51:18 PM(UTC)
Steffen Eriksen
Rank: Advanced Member

Groups: extranet\Forum
Joined: 5/11/2012(UTC)
Posts: 12

Hi all you smart people :)

Again a odd question from me. Lets say you have these accounts (Dimensions) in your chart of account:

10000 - Revenue (Headline)
10001 - Sale EU
10002 - Sale DK
10010 - Total Revenue (Sum 10001..10010)
20000 - Production cost (Headline)
20001 - Cost
20010 - Total Production cost (Sum 20000..20010)
20011 - Contribution I (10010-20010)
30000 - Salary
40000 - Property
50000 - Marketing
50000 - Contribution II (20011-30000-40000)

And lets say you have these departments (Dimensions):

100 - Sale
200 - Purchase
300 - Administration
400 - Marketing
500 - Production

Now we want to show the above accounts attached with different departmens like this:

Account - Name - Department
10001 - Sale EU - 100,200,300
10002 - Sale DK - 100,300,400
20001 - Cost - 500,200,400

etc, I hope you get the point. Is this possible in TARGIT??

I have seen other BI product that are able to do this and wanted to hear if this is possible in TARGIT. I also know one solution is to build in your DW a setup where every account has it own departmens dimension attached; ex:

Account - Name - Department
10001 - Sale EU - 100
10001 - Sale EU - 200
10001 - Sale EU - 300
10001 - Sale EU - 400

Currently using version 2011,
Klient version 2K11 build 4614
Server version 2K11 build 4614

Looking forward to your answer.

Steffen
Páll Bjarkason  
#2 Posted : Friday, October 26, 2012 1:16:11 PM(UTC)
Páll Bjarkason
Rank: Advanced Member

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

Hi Steffen
The most common combination of account at and department are these two:

Measure per Account by Department
UserPostedImage

Measure per Account and Department
UserPostedImage

But I have a feeling that neither dimension combination is quite what you want…
Best regards

Páll, CTS
Steffen Eriksen  
#3 Posted : Friday, October 26, 2012 1:28:30 PM(UTC)
Steffen Eriksen
Rank: Advanced Member

Groups: extranet\Forum
Joined: 5/11/2012(UTC)
Posts: 12

Hi Páll

Thanks for the reply and yeah you are quite right.

If you want to show department Denmark only on account 4030000 SALES - RETAIL and not on 5030000 COS - RETAIL PRIMARY, how do you manage this :)

Steffen



Páll Bjarkason  
#4 Posted : Friday, October 26, 2012 1:54:33 PM(UTC)
Páll Bjarkason
Rank: Advanced Member

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

Hi Steffen

Still not quite sure what your problem is.

Measure per Department and Account
UserPostedImage

Using a criteria (local or global) you can omit certain departments and/or accounts...
Based on TARGIT demo data, all accounts greater than 500000 are omitted in the above example.

Best regards

Páll, CTS
Steffen Eriksen  
#5 Posted : Friday, October 26, 2012 2:35:22 PM(UTC)
Steffen Eriksen
Rank: Advanced Member

Groups: extranet\Forum
Joined: 5/11/2012(UTC)
Posts: 12

Hi again

My bad, I didn't made my self clear in the last post.

If you want to show the amount on department Denmark
on account 4030000 SALES - RETAIL and not on 5030000 COS - RETAIL PRIMARY
AND show department Canada
on account 5030000 COS - RETAIL PRIMARY and not on account 4030000 SALES - RETAIL.

BR
Steffen
Páll Bjarkason  
#6 Posted : Monday, October 29, 2012 8:40:15 AM(UTC)
Páll Bjarkason
Rank: Advanced Member

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

I don't know if it is possible to do what you want directly in the TARGIT BI Suite, without some tweaks to the ETL/DW...

One thing you could do is to use multiple objects and global criteria to make the relationship between account and department clearer.

In the example I have used account as my global criteria. The chosen accounts are displayed in the colored top object. Accounts are listed in bottom left object and departments are listed in bottom right object. In the department object I have used comparisons to highlight the account 403000 and accounts equal or greater than 490005.

Using multiple objects you give your user a powerful tool and allot of freedom to discover complex relationships in data.

UserPostedImage
Best regards

Páll, CTS
Steffen Eriksen  
#7 Posted : Wednesday, November 7, 2012 5:22:35 PM(UTC)
Steffen Eriksen
Rank: Advanced Member

Groups: extranet\Forum
Joined: 5/11/2012(UTC)
Posts: 12

Hi Páll

Thanks for your answer and sorry for my late reply. I don't know if I can follow you but I might have another approach. Lets say you create 3 different analyzis based on the same measures and dimensions. Are you then able to combine them across analyzis?

Ex:

Analyze 1
Account 10000
Dimension 100 and 200
Amount = 500,-

Analyze 2
Account 20000 Dimension 300
Amount = 350,-

Analyze 3
Analyze 1 + Analyze 2
500,- + 350,-

Best regards

Steffen
Páll Bjarkason  
#8 Posted : Friday, November 16, 2012 11:46:47 AM(UTC)
Páll Bjarkason
Rank: Advanced Member

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

Hi Steffen
This has become a long thread but I will try one more post :)
I will give an example of how you can build a big analysis and then splitting the analysis up into smaller pieces by hiding elements such that you get a varying number of rows in the part analysis as you originally wanted (no empty cells).

The original dimensional output from Revenue per Customer Country by Item.
UserPostedImage


After hiding T-SHIRT and UNDERWEAR one can calculate specific cells and get the new total.
UserPostedImage

Hiding revenue only the calculated part of the analyses is visible.
UserPostedImage

Only showing JEANS Asia & Europe (blue)
UserPostedImage

Showing the totals for each Country (red)
UserPostedImage


The analysis now only shows the tow last objects (blue and red) which are a sub-set of the "big" analysis in the first picture. You could also show SHIRTS and any other ITEM (see the first picture in this post).
Don’t think it is possible to calculate a sum between different objects. But as shown in this example, you can build one analysis and only show sub-sets in different objects if you want to.

I my last post I indicated that:
Using multiple objects you give your user a powerful tool and allot of freedom to discover complex relationships in data.
Adding a dimension to the Criteria bar gives the end user the power of scope by enabling him to choose which accounts should be a part of the analysis.
And by clicking on a dimension the end user can for example drill down on Canada in one object and then the other objects in the analysis will drill down on that criterion too.

Edited by user Friday, November 16, 2012 11:54:34 AM(UTC)  | Reason: Not specified

Best regards

Páll, CTS
Steffen Eriksen  
#9 Posted : Friday, January 4, 2013 4:28:08 PM(UTC)
Steffen Eriksen
Rank: Advanced Member

Groups: extranet\Forum
Joined: 5/11/2012(UTC)
Posts: 12

Hi Páll

I solved this with comparison functionality but thank you very much for the help - you got me in the right direction.

Steffen
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