aggr by customer not efficient - suggestion for expression?

[kristof_j]

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.

[MarcoARaymundo]

Hi!

I think this is wont you are looking for

[kristof_j]

It's not exactly what I want 🙂

I think your model calculates the customers who used one of the channels. But we need the combination.

If I select Airplane, Bike and Bus I get all the customers despite of the fact that none of them used Airplane.

[MarcoWedel]

Search for "AND-mode"

[kristof_j]

Marco

We tried And-mode but it doens't work well if you select months.

If you make and-tables with Customer (=key) and Transport then time isn't involved so it doenst work if you select a specific period.

If you make and-tables with (Customer + Month) (=key) and Transport then time is too much involved

If a customer used 3 transportation over 3 month and you select the first type of transport then the and-tables will only work for other transportations in the month of the first selected transportation.

Maybe our and-table weren't correct, so if you have a working one ...

[MarcoARaymundo]

Hi!

Kristof look this.

In the dimensions uncheck the Hide null

[kristof_j]

Marco

I don't understand your dashboard 🙂

How do I use it?

It seems complex, do you think it will work with 4million customers and 20million uses of transport?

[MarcoARaymundo]

Hi Kristof!

What do not you understand?

To use is simple, select the means of transportation that you want to analyze and Model 4 table, you have to analyze how many people use each mode of transport and how many people do not use the same means of transport.

I think so. however, the performance will depend on the level of detail of the information you need.

The amount of transportation is a critical point, if there are many. However, if the analysis of transport are always at most 3, you can change the keywords to make them dynamic.

Finally, my proposal is one of many models that can help solve the problem, and as a model, you need to test it in an environment close to reality.

Marco

[kristof_j]

I don't see where I can find how many customers had the combination of the selected transportation.

If I select Bike, Bus I see in your table 8 no bike, 9 no bus. What does it mean? There are only 3 customers, correct?

• 3 customers used a Bike
  • 2 customers used a Bike and Bus
  • 1 customer used only a Bike