Dimension Problem

[lucas4bi]

Hello everyone,

i've got a question about this situation, i have 2 tables:

Customer
CustomerId
ActivationDate
ActivationYear
ActivationMonth

Ticket
CustomerId
TicketDate
TicketYear
TicketMonth

The 2 tables are linked via CustomerId, and i have to calculate a pivot table with 2 particular expressions:

The first one is simple, count the number of tickets per date, which is simple using TicketYear,Month and Week as dimension and counting the TicketId.

But in the second expression is required to calculate the number of customers which have been ACTIVATED during the period of "Ticket Creation" i'm looking at.

This means that if i'm looking at the number of tickets created on the 25th week of the year, i need to have on the second expression the number of customers activated on the same week, but the dimension i have to use are still the ones related to the ticket.

I can't split in 2 pivot tables using different dimension, cause it's required and needed to have all in the same pivot.

Does anyone ever experiences something to accomplish this  without messing up the tables structure in the script?

Thanks a lot

[swuehl · Accepted solution]

One possible solution is to use a data island, a table that just holds a calendar (Date, Week, Month, Year) for the period you are looking at.

Then use this data island as time dimensions. In your expressions, you'll need a condition to check your ticket dates vs. the data island date resp. the activation date vs. the data island date.

Maybe like

=count(if(TicketDate = IslandDate,TicketDate))

=count(if(ActivationDate = IslandDate, ActivationDate))

where your dimensions are IslandYear, IslandWeek, IslandDate  etc.

See also Henric's recent design blog post on how to build calendar tables.

Regards,

Stefan

[lucas4bi]

I had already created the IslandTable, but i wasn't using it properly on this specific object.

Thanks to your post now i see how easily i can solve this

[swuehl]

Another idea might be to create a concatenated table from your two tables, like

TABLE:

CustomerID, Date, DateType

where DateType is either Activation or Ticket, and link Date field to a master calendar.

Then you might be able to use this master calendar fields as dimension and a set expression to filter the count of Tickets or Activations:

=count({<DateType = {Activation}>} CustomerID)

=count({<DateType = {Ticket} >} CustomerID)