en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Justin Haber  
#1 Posted : Monday, March 2, 2015 4:09:38 PM(UTC)
Justin Haber
Rank: Member

Groups: extranet\Customer
Joined: 2/6/2015(UTC)
Posts: 7

Hi,

I have a list of item numbers with an 'in stock' measure against them. This list also includes some negative numbers.

The list in total shows 288,493 pieces in stock (which includes the negative numbers), but when I hide the members where the individual values are negative, whilst it removes the item numbers from show, it does not amend the total - instead it just stays the same.

Once I have removed the negative values, my total should actually increase to 288,627.

Can you help?
Ole  
#2 Posted : Monday, March 2, 2015 7:46:09 PM(UTC)
Ole
Rank: Administration

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

Hi Justin,

What you are seeing is the symptom of individual member values and Totals being retrieved from the database as individual entities. When you hide some of the individual members in the client, the database Total "does not know" about this, and will still show the original total.

You should add your own calculated Total to your crosstab. You may try with the predefined Total calculation from the Calculations tab. In more recent versions it should automatically pick up only the visible members. If you are still seeing a problem, you may try with a Custom calculation, as a new row, like this:

sum(0, all(v), 0)

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
Justin Haber  
#3 Posted : Wednesday, March 4, 2015 1:00:12 PM(UTC)
Justin Haber
Rank: Member

Groups: extranet\Customer
Joined: 2/6/2015(UTC)
Posts: 7

Thanks Ole, the custom calculation option works for me.

For me, individual SKUs belong to a Brand hierarchy, where you can drill into the Brand to expose the individual SKUs that are attributed to it.

Whilst I can hide the individual SKUs that have a negative number, the total for the Brand however does not change once I have hidden specific SKUs, as this is on a hierarchical collation.

Any ideas how to get the Brand total to amend once I've hidden certain SKUs?
Ole  
#4 Posted : Wednesday, March 4, 2015 1:25:32 PM(UTC)
Ole
Rank: Administration

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

All right. Same problem. Different solution.

The problem is still due to totals and subtotals being retrieved from the database and the database not knowing that you have hidden something in the client.

The solution I propose here is for a two-level hierarchy:

1. Keep the Total calculated row you did previously.
2. Add a new, custom calculation, as a calculated column:

if allcount(d1, all(c), m1) = 0 then sum(d1, 0, m1) else sum(d1, all(v,c), m1)

3. Optional: Go into your calculated Total, "Calculate in intersection", to include the new calculated column in the Total calculation.

Calc explanation: If you are at a node without children, then simply transfer the value, otherwise if you are at a node with children (the subtotal nodes) then sum up all the visible children.

The solution complexity will increase if you have deeper hierarchies or if you have dimensions on the horizontal axis of your crosstab. 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
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