Redundant dimension that adds more than 100%

A quite common situation is usually to have to generate a graph (or table) where the classes of a dimension are completely or partially overlapped.

For example consider the following graph that represent customer’s complaints:

 Grafico

It may happen that a customer is female, male or Legal Person. But now suppose we want to display a subtotal with the quantity of claims of physical persons. This means that each claim of a man going to “add” to “Man” bar but ALSO to the “Physical Person”, and each claim of a woman join to the bar “Woman” and simultaneously to the “Physical person ” too. Similarly all claims add up to the bar “Total”.

For this we create a “redundant” (overlapped) dimension  (I don’t know if it’s the right term) like this:

Dimension 

Where is seen that “Customer Type” = 1 (the key by which the fact table will link) is described both as “Man,” as well as “Physical Person”

Data Model

Below the code and chart showing some additional expressions:

Dimension:
load * inline [
CustomerType, Class, Clase
1,   Man, Hombre
2,   Woman, Mujer
3,   Legal Person, Persona Juridica
1,   Physical Person, Persona Fisica
2,   Physical Person, Persona Fisica
1,  Total, Total
2,  Total, Total
3,  Total, Total
]
;

Claims:
Load * inline [
CustomerID, CustomerType, AmountClaimed, Name
1, 1, 100, Juan
1, 1, 80, Juan
2, 3, 220, Microsoft
3, 2, 100, Maria
4, 2, 80, Sofia
5, 1, 220, Peter
6, 3, 100, Oracle
2, 2, 80, Silvia
6, 1, 220, Steve
]
;

Final dashboard:


Tablero
 

Acerca de pparnisari
Casi 44 vueltas al sol. Licenciado en sistemas. Curioso, investigador, excelente para resolver problemas prácticos. Casado, 2 hijos, cada vez con mas preguntas y respuestas mas en duda. Almost 44 laps to the sun. System engineer. Curious, researcher, great for solving practical problems. Married, 2 children, each time with more questions and more answers in doubt.

Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: