Load script full variable.

[kris_vliegen]

Hi All,

I would like to create a script that is full variable.

Some users would like to choose the number of Selections, And alse on what fields the selections are made and what value.

For example

one time, They would have a dashboard withe the customers who have a revenue from more than 10000€

but also the customers who haver ordered more than 30 different articles (no mather what the revenue is)

The nect time they would look at the customers who didn't order this year any (ore less than 5 different articles) but who did order them in the past years or the customers who are situated in one region, ...

I think that I need to work with variable where clausules. But I have no idea how I can make it full variable.

Every time they would look at other statements I have to schedule a job that create a dashboard with the where clausules they ask for.

Who has experience with this?

Regars,

Kris

[marcus_sommer]

I'm not sure if this will be very useful and practically then you would create a BI logic from the old days where user selects some parts of a data-cube and execute the query on their demand. The native logic from qlik is different by loading (nearly) all data within one app and the user selects there which data and views are needed.

- Marcus

[kris_vliegen]

Hi Marcus,

So you wouldn't do this with Qlikview.

But what is peaople want's to evaluate the customers who have a revenue from more than 10000€ or the customers who have bought more than 30 different articles?

Because with the first selection you filter a number of customers, but withe the second selection we should get more customers...

Regards,

Kris

[marcus_sommer]

You need to implement most of respectively the more important user-requirements within the gui. This isn't always trivial but a lot easier than implementing those requirements within variable load-queries.

One way to enable the user to query such questions are listboxes with expressions like these:

=class(aggr(sum(Revenue), Customer), 500)

=aggr(rank(sum(Revenue), 4), Customer)

whereby the used measures and dimensions and the bucket-size from the class could be other selections or variables and therefore very flexible. Similar approaches could be also implemented directly within the chart-objects.

- Marcus

[kris_vliegen]

Hi Marcus,

thanks for the response but how can we create an "or" filter?

Because we wan't to see a list of users who have a revenue from more than 10000€ or who have bought more than 30 different articles. (Even if the revenue is less then 10000€)

With your expressions I can create the filters. But I would like to have "or" instead of "And"

Regards,

Kris

[marcus_sommer]

Maybe in this way as listbox-expression:

if(aggr(sum({< [Categorie] = {'Any'}>} Revenue)>10000 or

          count({< [Categorie] = {'Any'}>} distinct Article)>30,

   Customer, Month, Year), 'good one', 'bad one')

or as calculated dimension in a chart:

if(aggr(sum({< [Categorie] = {'Any'}>} Revenue)>10000 or

          count({< [Categorie] = {'Any'}>} distinct Article)>30,

   Customer, Month, Year), Customer)

with considering further dimensions like month and year and further (optional) conditions within a set analysis.

- Marcus

[kris_vliegen]

Hi Marcus,

But if the expression change, we have to edit all the list-boxes and all the charts.

The users don't have any technical experience.

That's why I was thinking on a way to manipulate the loadscript.

And restart a reload after the selections are known. So we can create a new Qlikview-file with all the data that fits the selections.

Regards,

Kris

[marcus_sommer]

Even with your mentioned reload-approach with filters to the set the selections you will need some (a lot) efforts to build a nicely working gui to the various possibilities which the user could filter. Further you will need a lot of variables and logic to transfer the selections as where-clauses and aggregations to the right positions within the script and a reload will be always a break within the workflow from the users.

Therefore my suggestion to solve this only within the gui and a listbox-expression like above mentioned could be flexible and very easy to use even for non-technically user. For example:

if(aggr(sum({< [Categorie] = {'Any'}>} Revenue)>10000 or

          count({< [Categorie] = {'Any'}>} distinct Article)>30,

   Customer, Month, Year), 'good one', 'bad one')

could be look like:

if(aggr(

     [$(=getfieldselections(AggregationType))]({< [$(=getfieldselections(Dimension))] =      {'Any'}>} [$(=getfieldselections(Measure))]) > $(vSizeLowerMeasure) or

     count({< [$(=getfieldselections(Dimension2)] = {'Any'}>} distinct [$(=getfieldselections(Measure2))]) >      $(vSizeSecondMeasure),

   Customer, Month, Year), 'good one', 'bad one')

and be very dynamic only by selection some extra dimensions which contain the fields, measure and aggregations which should be used and some variables to control the upper/lower limits from the expressions.

Within the qlik demo "What's new" is an example of such a logic and if you searched here within the community you will find various examples how it could be implemented, for example:

User Controlled Charts In QlikView

Dynamic charts

- Marcus