en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Páll Bjarkason  
#1 Posted : Friday, January 13, 2012 11:56:27 AM(UTC)
Páll Bjarkason
Rank: Advanced Member

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

I want to follow a group of client’s one year. The criterion for who shall be included in the group is that they have used some service in January 2010. The service utilization is represented in hours as a measure.

Therefore I taught of using a visibility agent and stating that the value of service utilization is less than 0. But how can I specify that it is the service utilization value for January 2010 that must be less than 0?

I’m only interested in the sum each month and I don’t want invisible client’s hours to be part of it.

I want to do the same exercise for 2011. Is this possible in the same analysis?
Best regards

Páll, CTS
Kho Wei  
#2 Posted : Tuesday, January 17, 2012 4:52:54 AM(UTC)
Kho Wei
Rank: Newbie

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

Hi. Based on my understanding, these are the steps that I did.
1. Create a cross table. Customers on Rows. Years on Column. Inserted a measure (Units Sold).
2. Selected 2010 and 2011 in the Year Criteria Bar.
3. Create a new calculated column in Targit Advanced Calculations called "Last Year's Customers". The formula is this:
if sum(d-2, 0, m1) <> 0 then 1 else 0
This means that if 2010 have sales (not zero) then show as 1, indicating that this customer is also last year's customer. Else, show 0, indicating that they are not last year's customers. Do note that if you are expanding down to Month level, the cell references will change. Quite likely that the new formula will look like if sum(d1, 0, m1) <> 0 then 1 else 0, assuming January is always the first month of Last Year.
4. Use visibility Agent to hide not last years customers, which are customers with "0" for the calculation.

This can be improved further if you use Targit's Dynamic Periods so that it will always show the latest 2 years.
You can also consider hiding the calculated column "Last Year's Customers", or other non-related months in the last year (if viewing data at the month level).
To create a total or sum of all visible customers, i.e. last year's customers, you can create a calculated row with the following formula:
sum(0, all(v), 0)

The following is the output and the working on achieving this.

Output:
UserPostedImage

Working (in Advanced Calculations, the shaded rows are the hidden rows using Visiblity Agent):
UserPostedImage

Edited by user Tuesday, January 17, 2012 5:12:58 AM(UTC)  | Reason: Not specified

Páll Bjarkason  
#3 Posted : Thursday, February 16, 2012 4:54:49 PM(UTC)
Páll Bjarkason
Rank: Advanced Member

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

Tanks Kho Wei. Your post was very helpful!

In calculations I use the formula:
Code:
if sum(d-2, 0, m1) <> 0 then 1 else 0

And in the visibility editor I use (hiding):
Code:
sum(d-2, 0, m1) <= 0

Now I can follow a group of client’s over a period of time based on the criterion that they have used some service in in a specific time period!

This is (a part of) the table I get.
UserPostedImage

Now I want to present “sum hours” and “count customer” as a figure:
UserPostedImage
The problem is that I have already used visibility agents to solve my problem. And the diagrams visibility settings interfere with the underlying table messing up my result :(

Ideas anyone?

Edited by user Thursday, February 16, 2012 5:20:56 PM(UTC)  | Reason: Not specified

Best regards

Páll, CTS
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