27 Replies Latest reply: Jul 16, 2014 8:23 AM by Kristof Jacobs

# 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.

• ###### 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

• ###### 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

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

This is my suggestion:

• ###### 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.

• ###### 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

• ###### 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.

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

Definitely the tall thin data model. See attached.

Hope it helps,

Jason

• ###### 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.

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

How does my solution not allow for this?

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

Well we need to be able to select the 3 types of transport.

Say that we have a list of Car, Taxi, Bus, Foot, Bike, Boat ... I then select Car, Taxi, Bus, the expressions should show that we have only customer A who has used these 3 types.

But if we select month 2, it should show that none of the customers used the 3 types.

And to make it more complex, we want to see all the combinations of the 3 channels.

So you'll get 8 combination (Car & Taxi & Bus, Car & Taxi, Car & Bus ...).

I think when we can solve the first problem we'll be able to solve this one.

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

Sorry Kristof - I don't follow the issue. My solution seems to be doing exactly as you ask.

With no selections the new table I added shows 1 customer used 3 types of transport and 2 customers used 2 types. When you select month 2, the table changes to show there is 1 customer with 2 types and 1 customer with just 1 type.  You can add as many transport types as you like and as many combinations as you like - the model will still work fine.

Jason

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

Yes but I wasn't very clear in what we really want.

We want the user to select 3 types of transportation.

• First he selects Car and we want to know which customers used a car, lets say 10 customers
• Then he select Taxi and we want to see the following:
• 10 Car
• 6 Taxi
• 4 no Taxi
• 5 no Car
• Then he select Bus and we see the following:
• 10 Car
• 6 Taxi
• 2 Bus
• 4 no Bus
• 4 no Taxi
• 1 Bus
• 3 no Bus
• 5 no Car
• 3 Taxi
• 2 Bus
• 1 no Bus
• 2 no Taxi
• 1 Bus
• 1 no Bus

You'll get a venn diagram with 8 combinations

But we want to leave the option open to change, lets say Taxi into Bike and change the period to the last 2 months.

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

That’s not very consistent…don’t you mean the following?

And do you actually want a Venn diagram output? Venn’s are notoriously difficult in QV. Your bullets below seem to indicate a pivot-table output.

•   First he selects Car and we want to know which customers used a car, lets say 10 customers

•   10 Car

•   5 no Car

•   Then he select Taxi and we want to see the following:

•   10 Car

•   6 Taxi

•   4 no Taxi

•   5 no Car

•   3 Taxi

•   2 no Taxi

•   Then he select Bus and we see the following:

•   10 Car

•   6 Taxi

•   2 Bus

•   4 no Bus

•   4 no Taxi

•   1 Bus

•   3 no Bus

•   5 no Car

•   3 Taxi

•   2 Bus

•   1 no Bus

•   2 no Taxi

•   1 Bus

•   1 no Bus

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

A pivot table would be an alternative but our users aren't very good with pivot tables and we are trying to make it visually more appealing.

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

So what exactly do you want to output, ideally? Given that the possibilities may expand you need something that will grow dynamically. Or you can create something fixed.

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

We want to see something like a pivot chart but then with text objects.

First a user selects a channel and he sees how many used the channel and how many not

• 10 car
• 5 no car

Then he select a second channel and sees:

• 10 Car :
• 6 Taxi
• 4 no taxi
• 5 no car
• 3 Taxi
• 2 no taxi

and then a third ...

But in a better visualization than a pivot chart, like a static venn diagramm. With static i mean that the leaves of the venn diagramm always have the same size but we start with 1 leaf and then 2 ...

This probably means that we should use 3 selection boxes with all the channels in an alternate state or data island.

And we want it to take the months into account.

If a customers used a Car in January and Taxi in February and selects all months --> customers used Car and Taxi.

But if the user selects February than the customers only used Taxi.

It's the time aspect that is keeping us busy.

We can solve this with an aggregation by customer but when you use is for 4 million customers then QV needs to calculate for several minutes.

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

Hi!

Look this model.

I hope to helo you!

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

Hi Marco

We should be able to select the channels dynamically.

In the model there are only 3 channels but we have maybe 9 channels and not all combinations are relevant.

We want to select 3 out of 9 channels and we want to be select the period.

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

Ok!

Try version 2

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

Hi!

I think this is wont you are looking for

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

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.

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

Search for "AND-mode"

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

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 ...

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

Hi!

Kristof look this.

In the dimensions uncheck the Hide null

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

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?

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

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

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

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