Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

kristof_j
Contributor II

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.

jason_michaelid
Honored Contributor II

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

Highlighted
kristof_j
Contributor II

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.

jason_michaelid
Honored Contributor II

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

kristof_j
Contributor II

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.

jason_michaelid
Honored Contributor II

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.

kristof_j
Contributor II

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.

thechacal
Contributor III

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

Hi!

Look this model.

I hope to helo you!

kristof_j
Contributor II

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.

thechacal
Contributor III

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

Ok!

Try version 2

Community Browser