en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Poul Erik Noerhave  
#1 Posted : Tuesday, May 17, 2011 2:09:37 PM(UTC)
Poul Erik Noerhave
Rank: Newbie

Groups: extranet\Forum
Joined: 5/17/2011(UTC)
Posts: 1

I have a hierachy with 3 level: Boilertype/projid/milestoneid with 2 measures projhours and linecount

I want to hide all projid's which has less than 6 milestone rows and then summarize all projhours and all linecounts per Boilertype. Then divide these sum's to get average projhour per Boilertype

I have hided the projid's with less than 6 milestone rows with success, but the sum of projhours per Boilertype still includes all projid's

How can i make a Custom Calculation with a sum per Boilertype for all visible rows ?
Dan Cowan  
#2 Posted : Monday, May 30, 2011 2:57:37 PM(UTC)
Dan Cowan
Rank: Advanced Member

Groups: extranet\Forum
Joined: 3/22/2011(UTC)
Posts: 4

It would be helpful to see an example of the information but it sounds like you are wanting to use a visibility modifier.

For example if you wanted to count or sum all visible (v) then it could be used as count(d-1,all(v),m1). Likewise a hidden (h) modifier could be used count(d-1,all(h),m1).

Other useful examples are mixing modifiers for visible children (v,c) ex.- count(d-1,all(v,c),m1).

There is an operator that is also useful ALLCOUNT, that is useful similar to a visibility modifier ALLCOUNT(d-1,all,m1) except that it counts all empty and non-empty vaules.

Hope this helps,
UserPostedImage
TARGIT USA Reseller of the Year 2011!
DAN COWAN
VP OPERATIONS & DIRECTOR OF SALES
dcowan@BusinessImpactInc.com
Proud supporter of YouTARGIT.com and TargitUsers.net

You have to understand business to build a business intelligence solution…
Ole  
#3 Posted : Friday, June 10, 2011 3:49:41 PM(UTC)
Ole
Rank: Administration

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

Hi Poul Erik,

I presume you are referring to the fact that after having hidden some rows, the subtotals and the grand totals still include values from these hidden rows.

The reason is that while you may have hidden something in the client, the subtotals and the grand totals are still retrieved from the database – which does not know of things have been hidden in the client.

However, there is a workaround to this.

Add a new calculated column to get correct subtotals:

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

Essentially, what this calculation does, is if we are at the lowest level then just transfer the value else (if we are not at the lowest level) sum up all the visible children.

If you have even more levels in your hierarchy or if you need to see the correct grand total (e.g. if it hasn’t been hidden by the visibility agent) you will need to add another calculated column, referring to the first calculated column, i.e.:

if count(c1, all(v,c), m1) = 0 then sum(c1, 0, m1) else sum(c1, all(v,c), m1)

Etc.

Finally you will want to use Format visibility to hide the original measure and potentially intermediate calculated columns to show only the last calculated column which should now display correct subtotals (and grand totals).
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