en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Morten  
#1 Posted : Friday, January 17, 2014 10:52:37 AM(UTC)
Morten
Rank: Advanced Member

Groups: extranet\Forum
Joined: 7/9/2013(UTC)
Posts: 39

Hi.

I have a challenge.
In this cross table I need to calculate "Produktivitet" as "Registrerede timer" * "Kapacitetsgrad 2" - but only where "Registrerede timer" is not 0. However as it is now, the "total row" calculate based on all projects with "Kapacitetsgrad 2". In this case I only wish to calculate for the projects with "registrede timer" is not 0.

Can this be done?

Afterwards I need to calculate "Debiteringsgrad" * "Produktivitet", so the to figures must be on the same line in the "total row"

http://screencast.com/t/qI9AYWDjou

Thank you.
Morten
Niels Thomsen  
#2 Posted : Saturday, January 18, 2014 5:08:21 PM(UTC)
Niels Thomsen
Rank: Advanced Member

Groups: extranet\Forum
Joined: 4/5/2011(UTC)
Posts: 103

Hi Morten

I assume that the problem is that kapatictetsgrad 2 is registered even though no actual hours have been registered?

If this is correct I guess you would need an extra column with an if sentence - making sure that the kapacitetsgrad 2 values are only transfered if hours have been registered.

That would be something like if sum(d1,0,m1)>0 then sum(d1,0,m2) else 0 - the precise syntax depends on whether kapacitetsgrad 2 is in fact a measure or a calculation.

With this new column you would then have a correct input for the calculation.

Hope it makes sense.
Hope it makes sense.

Best Regards
Niels Thomsen

https://dk.linkedin.com/in/ncthomsen


Morten  
#3 Posted : Monday, January 20, 2014 1:16:54 PM(UTC)
Morten
Rank: Advanced Member

Groups: extranet\Forum
Joined: 7/9/2013(UTC)
Posts: 39

Hi.

Thank you.
However, this does not change the value of the "total row".

In order to achieve my goal I need to be able to multiply "Debiteringsgrad" (95,86 %) with the correct "Produktivitet" sum.

http://screencast.com/t/aPH9efUF

Thank you.
Niels Thomsen  
#4 Posted : Tuesday, January 21, 2014 9:53:54 AM(UTC)
Niels Thomsen
Rank: Advanced Member

Groups: extranet\Forum
Joined: 4/5/2011(UTC)
Posts: 103

Hi again

The problem is that the totals shown in the crosstab are precalculated and not summed up on the fly.

To wrap up the issue I think you will have to do a calculated row with the syntax sum(0,all,0)


Also when editing this calculation you will need to specify calculate in intersection in the calculation to make sure that the calculated row includes the cell where you're calculated column meets your calculated row.

Hope it makes sense.
Hope it makes sense.

Best Regards
Niels Thomsen

https://dk.linkedin.com/in/ncthomsen


Morten  
#5 Posted : Tuesday, January 21, 2014 11:10:24 AM(UTC)
Morten
Rank: Advanced Member

Groups: extranet\Forum
Joined: 7/9/2013(UTC)
Posts: 39

Hi Niels.

You are correct and I already tried this.

However, this enters another problem - I need to multiply "Produktivitet" and "Debiteringsgrad" and when one is a calculated row and the other is a "Total row", I cannot do this. Can you solve that issue, too?

In this screendump, I have hidden entries, where "Registrerede timer" is 0.
http://screencast.com/t/YlAdnk1b0fQ1

Morten.

Edited by user Tuesday, January 21, 2014 11:11:23 AM(UTC)  | Reason: Not specified

Niels Thomsen  
#6 Posted : Tuesday, January 21, 2014 1:57:58 PM(UTC)
Niels Thomsen
Rank: Advanced Member

Groups: extranet\Forum
Joined: 4/5/2011(UTC)
Posts: 103

To reference a total like that you can use the level modifier:
sum(d1,d1(l0),m1)

In this case the first row at level 0 (all level) is retrieved.

So a calculated column could be made with a syntax like

sum(c1,0,m2)*sum(d1,d1(l0),m1)


Hope it makes sense.

Best Regards
Niels Thomsen

https://dk.linkedin.com/in/ncthomsen


Morten  
#7 Posted : Tuesday, January 21, 2014 2:10:22 PM(UTC)
Morten
Rank: Advanced Member

Groups: extranet\Forum
Joined: 7/9/2013(UTC)
Posts: 39

Hi Niels.

Thank you. That nailed it.
My end calculated colomn is
sum(c2, 0, m1) * sum(d1, d1(l0), m3)

Can you tell, why the individual numbers in "Debiteringsgrad" is 100,00%. I would think that it should say 95,86% all the way down?

Thanks, Morten
Niels Thomsen  
#8 Posted : Wednesday, January 22, 2014 8:48:29 AM(UTC)
Niels Thomsen
Rank: Advanced Member

Groups: extranet\Forum
Joined: 4/5/2011(UTC)
Posts: 103

I think I lost track of how debiteringsgrad is calculated :-)

Hope it makes sense.

Best Regards
Niels Thomsen

https://dk.linkedin.com/in/ncthomsen


Morten  
#9 Posted : Wednesday, January 22, 2014 8:58:19 AM(UTC)
Morten
Rank: Advanced Member

Groups: extranet\Forum
Joined: 7/9/2013(UTC)
Posts: 39

:)
Debiteringsgrad is a measure - it is not calculated by me.
Niels Thomsen  
#10 Posted : Wednesday, January 22, 2014 4:48:35 PM(UTC)
Niels Thomsen
Rank: Advanced Member

Groups: extranet\Forum
Joined: 4/5/2011(UTC)
Posts: 103

Well the most common reason for a measure to be the same all the way down is that it's unrelated to a certain dimension. To answer precisely in this particular case I guess I would have to see your datamodel.
Anyway glad to hear you got the job done :-)
Hope it makes sense.

Best Regards
Niels Thomsen

https://dk.linkedin.com/in/ncthomsen


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