en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Lars Holmquist  
#1 Posted : Friday, October 5, 2012 2:34:43 PM(UTC)
Lars Holmquist
Rank: Member

Groups: extranet\Forum
Joined: 9/7/2011(UTC)
Posts: 8

Hi

I am trying to build a crosstable with a calculated percentage with a comparison ("sammenligning"). But I am unable to construct the correct calculation due to the comparison.

The crosstable layout is as follows:

x axis: time (monthly) and a comparison (<1 dag and >1 dag)
y axis: departments (HR, Sales etc.)
measure: number of cases
comparison: based on a dimension that contains several attribues with ranges (0-1, 1-14, 15-35 etc.) The comparison group all these attributes in 2 groups: below or equal to 1 (<1) & above 1 (>1).

UserPostedImage

In the image the measure is hidden and only the calculation ("procent") is visible.

Using the standard percentage calculation I get a result based on the percantages pr. department (based on the y axis). I want to get the percentage distribution pr month. Example:

HR in january had 7 cases below 1 and 3 cases above. Which should be presented like this:
<1: 70%
>1: 30%

Any suggestions are highly appreciated!
Páll Bjarkason  
#2 Posted : Monday, October 8, 2012 11:29:49 AM(UTC)
Páll Bjarkason
Rank: Advanced Member

Groups: extranet\Forum
Joined: 1/13/2012(UTC)
Posts: 164

UserPostedImage

Supposing the table above, this example is based on HR in January.
Using ‘Custom calculation’ ‘As a new measure’ I evaluate the percentage of Less-cases in relation to (Less + More). I.e. for Less: 3/(3+7)%=30%

Assuming that one column is hidden for each month then (Less=m2, More=m3). I calculate the Less%:
sum(0, 0, m2) % (sum(0, 0, m2) + sum(0, 0, m3))
and the More%:
sum(0, 0, m3) % (sum(0, 0, m2) + sum(0, 0, m3))

If you want to calculate the contribution from one departments score this month compared to the same department’s year total you can try this:
sum(0, 0, m2) % sum(all(s), 0, m2)
This month’s m2 sum as % of all months sum this year for the m2 measure. The (s) sibling’s parameter on the column attribute handles which year the month belongs.

For more insight on calculations (As a measure, sibling etc.) see the great On-Demand webinar by Ole Dyring: TARGIT Tips & Tricks Webinar, March 2012 - Get familiarized with the formula syntax for client based calculations.

TARGIT Tips & Tricks Webinar, March 2012

Edited by user Monday, October 8, 2012 11:42:27 AM(UTC)  | Reason: Not specified

Best regards

Páll, CTS
Lars Holmquist  
#3 Posted : Monday, October 8, 2012 12:53:18 PM(UTC)
Lars Holmquist
Rank: Member

Groups: extranet\Forum
Joined: 9/7/2011(UTC)
Posts: 8

Hi Páll

Thank you very much for the reply. Your post was exactly the input that I needed!

The solution is: sum(0, 0, m1) % sum(all(s), 0, m1)

Why? When using the comparison a custom calculation is inserted for every column in the 'comparison, so the calculation needs to be evaluated using the sibling function.
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