en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
SO  
#1 Posted : Tuesday, September 8, 2015 7:53:05 AM(UTC)
SO
Rank: Advanced Member

Groups: extranet\Forum
Joined: 8/19/2014(UTC)
Posts: 88

I have a crosstab with a comparison to make four item groups. There are no other dimensions in the crosstab. Two of my measures are items cost and item quantity. In this crosstab I'm not interested in showing info about each item in the comparison groups, only the totals.

How can I calculate the "inventory value" for each item group? (item cost)*(item quantity). Must I add item nr as a dimension and then hide it in some way to be able to calculate it?
Ole  
#2 Posted : Tuesday, September 8, 2015 9:48:21 AM(UTC)
Ole
Rank: Administration

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

It depends how you want the Inventory Value calculation to be done. I presume it cannot be done on the Group Totals level - otherwise it would of course just be a simple calculated column: sum(d1, 0, m1) * sum(d1, 0, m2).

If it has to be e.g. an average of all the individual Item Inventory Values, then there is no way around but to include the Item No dimension in the crosstab (on same axis as the Groups). In that case, you will need calculations like this:

Calculated column 1: sum(d1, 0, m1) * sum(d1, 0, m2)
Calculated column 2: avg(c1, all(c), m1)

The, from Format Visibility you may hide Item No values and labels and Calculation 1.

Let me know if this helpful to you.

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
SO  
#3 Posted : Tuesday, September 8, 2015 11:21:36 AM(UTC)
SO
Rank: Advanced Member

Groups: extranet\Forum
Joined: 8/19/2014(UTC)
Posts: 88

Originally Posted by: Ole Go to Quoted Post
It depends how you want the Inventory Value calculation to be done. I presume it cannot be done on the Group Totals level - otherwise it would of course just be a simple calculated column: sum(d1, 0, m1) * sum(d1, 0, m2).

If it has to be e.g. an average of all the individual Item Inventory Values, then there is no way around but to include the Item No dimension in the crosstab (on same axis as the Groups). In that case, you will need calculations like this:

Calculated column 1: sum(d1, 0, m1) * sum(d1, 0, m2)
Calculated column 2: avg(c1, all(c), m1)

The, from Format Visibility you may hide Item No values and labels and Calculation 1.

Let me know if this helpful to you.

BR / Ole


Thanks.

It's correct that it cannot be done on the group totals level.

What I need is the total inventory value for each comparison group. If I understand you correctly I should use sum(c1,all(c),m1) in calculated column 2?
Ole  
#4 Posted : Tuesday, September 8, 2015 11:29:36 AM(UTC)
Ole
Rank: Administration

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

That is correct. I didn't know if you needed the Total Inventory Value or the Average Inventory Value.

Total: sum(c1, all(c), m1)
Average: avg(c1, all(c), m1)

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