topic Re: Filtering chart data in QlikView

Filtering chart data

simonb2013 — Wed, 02 Apr 2014 13:45:16 GMT

So I have a table of data, always a good start !

Here's the columns:

countryID, PeriodDate, ItemID, ItemValue, ItemF1, ItemF2, ItemF3.....

On my 'dashboard' I am displaying a chart for each countryID, thus ....

Chart_1:

Dimension : PeriodDate

Expression : myCount = Count( distinct {<countryID = {'France'}>} itemID)

Chart_2:

Dimension : PeriodDate

Expression : myCount = Count( distinct {<countryID = {'Germany'}>} itemID)

and all this works wonderfully, my users get presented with lovely graphic for each and every country.

I now want the user to be able to examine the full data behind each chart, without filtering the entire application, or indeed affecting the other charts.

I created a new tablebox, and assigned both the 'France' chart and tablebox to alternate state 'myFranceState'.

On the good side, if I now select a PeriodDate in the chart, this is reflected in my tablebox.

The Problem:

My chart is not filtered by CountryID='France'.

I have only considered the CountryID in an expression.

Hence all the other countries are still in the dataset underlying the chart and such a filter is not shared between chart and table.

Is there a way to define a subset of data to build my chart on ?

Without filtering the entire app ?

many thanks

Re: Filtering chart data

Gysbert_Wassenaar — Wed, 02 Apr 2014 14:05:22 GMT

It's not clear to me what you added to which alternate state. If you have a chart for France and added it to myFranceState did you hard code the countryID to France in the chart expressions? If not it will show data for all the countries unless you select France in an object assigned to the same alternate state. Selecting France in a listbox in the default state won't be applied to objects in other alternate states.

Re: Filtering chart data

simonb2013 — Wed, 02 Apr 2014 14:49:44 GMT

ok, i'll try to be more succinct

My data contains countries 'france','germany','spain' .... etc

I am trying to make a chart & table operate only for the 'france' data.

Hence I have created a myFranceChart, and a myFranceTable, and assigned both to myFranceState.

However, I do not know how to 'restrict' the data to purely 'France' records..

For the chart, I can calculate only France values using set analysis : Count( distinct {<countryID = {'France'}>} itemID)

However, that does not affect the underlying datasource of the chart, that source still contains all countries.

Hence my myFranceTable, still lists data for all countries.

I need to filter the whole of the myFranceState to 'France'.

I realise I could do this by adding a ListBox to myFranceState, but then I would have to make it locked and invisible.

Is there not a way to simply filter the base recordset by a field-value ?

Hope that's clearer ?

Re: Filtering chart data

jonathandienst — Wed, 02 Apr 2014 14:59:13 GMT

Simon

Why not put a country list box on the dashboard? That way the user can select any country and the entire dataset is filtered accordingly. All objects reflect the filtered dataset (other than those that explicitly override the country selection using the set expression above).

If you would like to automatically select a country based on some sort of trigger, you can use an action to make the country selection automatically, and again the all the data would be filtered.

HTH

Jonathan

Re: Filtering chart data

Roop — Wed, 02 Apr 2014 15:13:37 GMT

Hi,

can you not use another chart object to look like a tablebox. This can then be fitered or you can put a simple rule instead of the dimension to say:

if(CountryID = 'France', FieldName, null())

This will just give you the French values

Hope this helps

Re: Filtering chart data

simonb2013 — Thu, 03 Apr 2014 13:26:38 GMT

Thanks for all of your responses.

To be honest none do exaclty what I want, but at least it tells me that I might be asking something 'not normal' and maybe I might need to rethink my plan.

Jonathan, yes this works... but I really wanted something hard-set, rather than an option that users can fiddle with. This chart/table must always only show France, and another chart/table pair must always show 'Germany' etc ..

Rupert, again, it would work, but I have 100+ fields in my data... so to create each field a san experission would be a nightmare to maintain. I will have < 10 of these to set up, so that's 500+ fields to wite as individiual expressions.

Again, I'm new to QV so I might be expecting too much ... but I'm suprised there isn't a more simple straight forward way to create a subset of data that you can then feed from.

Thanks for the input