en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Reidar Beck  
#1 Posted : Thursday, March 20, 2014 10:31:39 AM(UTC)
Reidar Beck
Rank: Member

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

Hi all,

I have this analysis that uses criteria to get temporary results that I again use for a calculation called "Svømmer Værdi". The analysis(calculation) is correct for every product number, but the standard Subtotal property shows the wrong summation I.E. 305,17 + 0 gives 610,34.

Does anyone know what the problem could be here?



UserPostedImage
Ole  
#2 Posted : Thursday, March 20, 2014 10:54:10 AM(UTC)
Ole
Rank: Administration

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

Hi Reidar,

This is because TARGIT calculations by default calculates results on "the current level". In your example, looking at the Subtotal level, it does the calculation like this:

(sum(d4, 0, m3) + sum(d5, 0, m3)) * sum(d4, 0, m4) = (1 + 0) * 610.34 = 610.34.

You might try this solution:

- Hide your Totals and Subtotals
- Add a new calculation as a calculated row: sum(0, all, 0)
- For the new calculation, enter the "Calculate in intersection" property to make sure that it is being used for the "Svømmer værdi" calculation.

This will work for a cross tab as shown in your screen shot. If you have crosstabs with multiple products (and thus multiple Subtotals) you might need a different solution. Let me know if that is the case.

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
Reidar Beck  
#3 Posted : Thursday, March 20, 2014 11:32:18 AM(UTC)
Reidar Beck
Rank: Member

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

Thanks for the fast and great reply, this works perfectly. And yes, the case is that we have multiple products in product groups. In the picture I only used one product group to make the problem clearer to see.

EDIT: I need to figure some way to figure out how to make this calculation correct when the product group is collapsed and when it isn't.

Edited by user Thursday, March 20, 2014 1:32:07 PM(UTC)  | Reason: Not specified

Ole  
#4 Posted : Friday, March 21, 2014 11:43:00 AM(UTC)
Ole
Rank: Administration

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

Unfortunately you cannot make this work with a collapsed product hierarchy. The problem is that the top level result is dependent on the values of its children. If you collapse the hierarchy, you effectively exclude the children values from your query result - and you can no longer calculate correct top level results.

If you can live with a more static solution, you can have two crosstabs: One showing children, and on where children have been hidden. To hide children (actually, to hide members on the lowest level, the level that does not have children itself) you can implement a visibility agent with this condition: count(d1, all(c), m1) = 0.

BR / Ole

Edited by user Friday, March 21, 2014 11:43:30 AM(UTC)  | Reason: Not specified

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
Reidar Beck  
#5 Posted : Monday, March 24, 2014 9:00:14 AM(UTC)
Reidar Beck
Rank: Member

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

Hi Ole,

Thanks again, the static solution will work just fine.
I can see the the right result for each product number and the total of all product numbers.

Now there is just one small issue left that I hope you can help me with. When Totals, subtotals and Product numbers are hidden the Product Groups are also hidden. What I hope to do now, is to make a calculated row showing the subtotals for the individual Product Groups, unfortunately I can't figure out how to to that. My thinking was to use have one calculated row for every product group and use "reference to dimension value", but as the name implies it only references a single value. Any ideas on how I can do this?

Ole  
#6 Posted : Monday, March 24, 2014 9:22:07 AM(UTC)
Ole
Rank: Administration

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

All right. In that case you might try an entirely different solution:

- Make sure that your Totals and Subtotals are visible again. (Drop the calculated row that I suggested in previous reply.)
- Assuming your "Svømmer Værdi" calculation is a calculated column that can be referred as c3, you should add a new calculated column, e.g. "Svømmer Værdi 2" with a syntax like this:
if allcount(c3, 0, m1) = 0 then sum(c3, 0, m1) else sum(c3, all(c), m1)
- You might even need to add another similar calculation to get the Grand Totals correct. Assuming the above added column can be referred to as c5, the next added column should be:
if allcount(c5, 0, m1) = 0 then sum(c5, 0, m1) else sum(c5, all(c), m1)

This solution will work for a table with multiple products and multiple subtotals. It will not make any difference to the issue with collapsing the hierarchy.

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