Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

kristof_j
Contributor II

aggr by customer not efficient - suggestion for expression?

Hi   

In my model I have customers who used several kinds of transportation each month.

I want to have a expression that can give me how many customers used a combination of transportation.

I should also be able to select a few months and that the expressions keeps these months in account.

It works with an aggregation by customer but when I use the expression on 4 million customers it takes hours to calculate.

Any suggestion for an expression or another model?

In the qvw I made 2 models.

I used the model where each transportation is in a different column.

The other model would be better because there I have more details like the color.

Tags (3)
27 Replies
eduardo_sommer
Valued Contributor

Re: aggr by customer not efficient - suggestion for expression?

Hi, Kristof

If I understood your problem, you need to group your customers that used the same combination of transportation in each month. In this case, using Car and Taxi is different from using Car, Taxi and Bus. Am I right?

How many different transportations are being considered for your application? If they are few, I think I have a way of simplifying yor analysis.

Let me know if I am in the right track.

Eduardo

kristof_j
Contributor II

Re: aggr by customer not efficient - suggestion for expression?

We have now 6 kinds of transportation and severel subcategories like the color.

In a few months we are thinking to add a few types of transportation.

And yes, using Car and Taxi is different from using Car, Taxi and Bus

jason_michaelid
Honored Contributor II

Re: aggr by customer not efficient - suggestion for expression?

Definitely the tall thin data model. See attached.

Hope it helps,

Jason

eduardo_sommer
Valued Contributor

Re: Re: aggr by customer not efficient - suggestion for expression?

This is my suggestion:

kristof_j
Contributor II

Re: aggr by customer not efficient - suggestion for expression?

Thx for your suggestion but a user needs to select a few types of transportation.

We have now 6 types and will be expending with more types.

Also a user could select several months.

Highlighted
kristof_j
Contributor II

Re: aggr by customer not efficient - suggestion for expression?

Thx but we need to be able to select several months.

If I select month 2 I'll still have 1 user that has all 3 transportations but in month 2 he only has 2 types.

A user also needs to select different types of transportation.

We have nog 6 types but we'll be expanding with more types.

If we make combinations than we'll have hundreds of possible different combinations.

eduardo_sommer
Valued Contributor

Re: aggr by customer not efficient - suggestion for expression?

I think that if you add the month to the group by and to the resulting table, it will work. The number of combinations is not a problem, because the transportations are being concatenated in alphabetical order. I'm answering using my tablet, where I can't change my qvd. But, as soon as I reach my laptop, I will make the changes and send it to you.

Eduardo

jason_michaelid
Honored Contributor II

Re: aggr by customer not efficient - suggestion for expression?

How does my solution not allow for this?

kristof_j
Contributor II

Re: aggr by customer not efficient - suggestion for expression?

I added month to the group by but then the model says that none of the customers had the 3 combinations.

What we want to do is that we select several types of transportation like Car, Taxi and Bus (or Bike, Foot, Boat...) and that it shows how many clients used those 3 types of transportation.

In my qvw it is only customer A.

But it should be able to take the months into account. If I select month 2 it must show that none of the customers used the combination of the three types.

Community Browser