Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Dimension Problem

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

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Dimension Problem

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

View solution in original post

3 Replies
Highlighted
MVP
MVP

Re: Dimension Problem

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

View solution in original post

Highlighted
Creator
Creator

Re: Dimension Problem

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

Highlighted
MVP
MVP

Re: Dimension Problem

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)