en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Lars Holmquist  
#1 Posted : Monday, February 27, 2012 1:39:18 PM(UTC)
Lars Holmquist
Rank: Member

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

Hi

I am trying to calculate the average of a measure based only on 80% percent of the availble members. But I am not able to find a good way to solve this requirement in Targit.

I have a measure [RegistrationTime] for the response time to enquiries represented as a duration in days. The rollup is designated as average, so that I can summarize responsetime for seperate sections of the business. This is all well and working.

In Targit the data is shown in a simple analysis with CaseID on the x-axis (nothing on the y-axis) and the actual measure. Another analysis is used to drill down on different sections of the business.

In the analysis I would like to only calculate the average based on the bottom 80% (to eliminate certain odd cases). I thought using a top-list would be the simple solution. But the top list for bottom 80% is calculated from the sum of days and not the ammount of cases which is want I want.

How can I hide the top 20% (based on the measure) from my case and thus calculate the average on the remaining bottom 80% ?
Páll Bjarkason  
#2 Posted : Tuesday, February 28, 2012 2:32:05 PM(UTC)
Páll Bjarkason
Rank: Advanced Member

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

I'm not quite sure that I understand the exact problem but I suggest this solution.

If you use the standard calculation – average, TARGIT calculates a new column with the same average in all rows.

You can see the average formula in “Advanced calculations” by right clicking on the column and choosing edit.
The average formula is:
Code:
avg(d-1["Source":f=0], all["Range":r:f=all], 0["Source"])

If you edit the formula to:
Code:
avg(d-1["Source":f=0], all(v), 0["Source"])

… you only calculate average on the visible members. Save the new calculation.

In Calculations then choose Toplist /Pareto analysis and choose bottom 80%.

Is this what you are looking for?
Best regards

Páll, CTS
Lars Holmquist  
#3 Posted : Wednesday, February 29, 2012 1:06:02 PM(UTC)
Lars Holmquist
Rank: Member

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

Hi Páll

Thank you for your suggestion. What you descibe is actually more or less the average measure that I have build in MDX. Let me try to explain my issue in more detail.

Below is an example of my current data in Targit. CaseID is a dimension with all the individual cases. RegistrationTime is a measure which calculates the time taken (in days) for a correct registration of the case. With the use of MDX this measure is aggregated as an average.

CaseID RegistrationTime
1001 4
1002 7
1003 9
1004 14
1005 20
1006 2
1007 3
1008 1
1009 7
1010 4

Using the "Totals" function in Targit I get an calculated average for the cases - ie. 7,10.

My customer has an KPI that 80% of their cases must be registered within 4 days. Therefore I would like to use the bottomlist to isolate 80% of the cases and then calculate the average of these cases (my MDX measure should be able to handle this average calculation automatically). Intended output:

CaseID RegistrationTime
1001 4
1002 7
1003 9
1006 2
1007 3
1008 1
1009 7
1010 4

Total: 4,63

My issue is, that the bottomlist removes the top 20% based on the measure values. I would like to remove 20% of the cases - ie. the 2 cases with the higest registrationTime (CaseID 1004 and 1005).
Ole  
#4 Posted : Monday, March 12, 2012 2:55:45 PM(UTC)
Ole
Rank: Administration

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

Hi Lars,

I have created a similar situation, based on TARGIT demo data:

UserPostedImage

The two calculated rows above are defined by Average = avg(0, all, m1) and Average visible = avg(0, all(v), m1).

Suppose that I would like to see only the 80% of salespersons with the lowest Number of Sales. First I will need to sort the cross tab data:

UserPostedImage

The two calculated columns are not really necessary, but I included to provide a clearer picture. Allcount = allcount(d1, d1:0, m1) and Percent = allcount(d1, d1:0, m1) / allcount(d1, all, m1).

Finally, we can use a Visibility agent to hide anything above 80%:

UserPostedImage

Notice that the "d1:-1" notation means that I am hiding a row whenever the previous row is above 80%. This is to ensure that the last visible member on the list just breaks the 80% threshold.

Having removed the Allcount and Percent calculated columns I get to a result like this:

UserPostedImage

Best regards,
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
Lars Holmquist  
#5 Posted : Wednesday, May 23, 2012 1:57:47 PM(UTC)
Lars Holmquist
Rank: Member

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

Hi Ole

Sorry for the long delay - I've been busy with other projects..

Anayway, your solution works brilliantly! Thank you very much.


BR Lars
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