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 : Thursday, September 13, 2012 5:17:25 PM(UTC)
Páll Bjarkason
Rank: Advanced Member

Groups: Guests
Joined: 1/13/2012(UTC)
Posts: 154

I have a crosstab with customersID x agegroup. See picture.

UserPostedImage

When the scope of the analysis is 1 year then many customers have had a birthday in the period. But not all of them. For example if a new customer arrives in July, he/she can already have had his/her birthday in April. This gives a mix where some customers are represented I one age group and others are represented in two age groups - cluttering the total number of clients. I.e. the total number of clients does not equal the total for the 3 age groups as 2.695 does not egual 2.601(A customer can also have had his/her birthday and not change age group).

The first 3 columns (d1:d3) are my original age group dimensions and the last 4 columns are calculated columns. Column 4 (c1) “# age groups pr. customer” is the sum of d1:d3. A customer with the value 2 is represented in 2 age groups and has to be handled (cleaned). I use underscore _ to remind myself that this is a calculated column. Column 5 (c2) “_Age -29 Cleaned” does the following.

if sum(d1, 0, m1) = 1 then sum(d1, 0, m1) - sum(d2, 0, m1) else 0

The condition simply states that if the value of columns 1 for “this row” is 1 then (column 1 – column 2) else write 0 as there is no value in column 1. I can presume this as a customer only can be represented once or not at all in one age group. Stating (column 1 – column 2) makes sure that a customer is not represented in both column 1 and column 2. He/she is pushed up in the next/older age group.
I repeat this logic for the middle age group and it goes without saying that the oldest age group does not need to be handled/cleaned.

The last column “_# age groups pr. Customer Cleaned” is simply checking that all customers only belong to one age group. The check is done by summing the 2 new calculated columns _Age -29 Cleaned, _Age 30-59 Cleaned and the original Age 60- dimension. When there is 1 in all rows all customers only belong to one age group. This is verified as sum equals allcount for the entire column.
I'm nicing the output via visibility. As i'm only showing the total for_Age -29 Cleaned, _Age 30-59 Cleaned and the original Age 60- dimension and editing Dynamic captions for relevant members.


The problem is……
Now I have to do the same exercise not on age groups but on actual age. I.e. 1,2,3,4…99,100…. This gives me about 100 columns and the method described here does not seem optimal.
I’m not proud to say that I solved the problem in Excel, but I would like to know if someone has an idea to how the problem can be solved in the TARGIT BI Suite. For example by returning the age group dimension value?

Edited by user Friday, September 14, 2012 10:20:32 AM(UTC)  | Reason: Not specified

Best regards

Páll, CTS
Ole  
#2 Posted : Friday, September 14, 2012 9:10:31 AM(UTC)
Ole
Rank: Administration

Groups: Guests
Joined: 4/7/2011(UTC)
Posts: 320

I have a simple approach to hide the unwanted age categories. I am assuming the requirement is to show only one age category per person and only the latest (the right most) age category. I am also assuming that whenever a person may belong to two age categories, these two age categories will be in two adjacent columns.

I will be using a “Hide Value” visibility agent to hide the 1’s in unwanted age categories:

sum(1, 0, m1) <> 0

This simple visibility agent will look into the cell to the right of the current cell, and if it contains a value, e.g. “1” then it should hide the value of the current cell.

Edited by user Friday, September 14, 2012 9:14:19 AM(UTC)  | Reason: Not specified

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
Páll Bjarkason  
#3 Posted : Friday, September 14, 2012 11:07:47 AM(UTC)
Páll Bjarkason
Rank: Advanced Member

Groups: Guests
Joined: 1/13/2012(UTC)
Posts: 154

This is a very nice solution to the problem, and your assumptions are spot on... I’m sorry to say that my problem actually is a little more complex than initially described.

Beside the age measure (m1) I also have the customer’s procurement measure (m2). Then, for one customer witch has his/her birthday, I for example have a total procurement at age 24 = 2.000 and age 25 = 3.000 (given the scope of the analysis).

I now want to assign all the procurement for the customer to age 25. I.e. age 25 = 5.000.
Getting the desired age affiliation, number of clients and the procurement correct I can calculate procurement pr. client (pr. age group), as shown in the table below:

UserPostedImage
Best regards

Páll, CTS
Páll Bjarkason  
#4 Posted : Friday, September 14, 2012 12:46:41 PM(UTC)
Páll Bjarkason
Rank: Advanced Member

Groups: Guests
Joined: 1/13/2012(UTC)
Posts: 154

Eating my lunch I solved the problem (I think).

First I use Custom calculation “as a new measure” and creating two new columns pr. age (I.e. c.a. 200 new columns).
Flag 0, 1, 2 ages:
if sum(0, 0, m1) and sum(1, 0, m1) <> 0 then 2 else if sum(0, 0, m1) <> 0 then 2 - count(all, 0, m1) else 0
This gives information on where the ages are and if there are 0, 1, or 2 adjoining procurements.

Customer’s total procurement:
if sum(-1, 0, m2,0) = 2 then sum(-1, 0, m1) + sum(0, 0, m1) else if sum(0, 0, m2) = 1 then sum(0, 0, m1) else 0
Then I can use your logic and make the desired sums for the 3 cases by evaluating the flag above if there are 0, 1 or 2 adjoining procurements.

NB sum(-1, 0, m2,0) the fourth parameter cero handles the undefined case when in the left most column, trying to look one more column to the left. If there is no column on the left hand side I don’t need the flag 2 case as it’s certain that there is no value in a non-existing column. Therefor it is ok to state 0=2 which always will return false.

Thank you for the inspiration.

Edited by user Monday, October 8, 2012 2:34:23 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