  en Previous Topic Next Topic
 Lars Holmquist #1 Posted : Friday, October 5, 2012 2:34:43 PM(UTC) Rank: MemberGroups: extranet\Forum Joined: 9/7/2011(UTC)Posts: 8 HiI 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 casescomparison: 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). 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) Rank: Advanced MemberGroups: extranet\Forum Joined: 1/13/2012(UTC)Posts: 183 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) Rank: MemberGroups: extranet\Forum Joined: 9/7/2011(UTC)Posts: 8 Hi PállThank 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.  