topic Filtered Query in QlikView

Filtered Query

qlik_venu — Thu, 17 Jun 2010 09:56:22 GMT

Hi,

We need to filter the records for a indivudal sheet alone.

We have almost 10 sheets in the qvw file. All of the sheets uses a query which would return 10 years of data. from 2000, 2001, 2002....2010.

But for just one sheet we would need to have the current year records alone.

these sheets are basically charts which have the time dimension on the x-axis. So for only 1 sheet we would need just the 2010 data. whch would be having the months jan, feb. mar....dec just for the currrent year 2010.

We can do this from the Edit script menu where in we can add a seprate Query and build new Column names which can be reffered in the current year Sheet.

But we wuold not prefer this as it would create columns with Similar column names. is there a way by which we can filter reords in the sheet level.

Thanks,

Venu

Filtered Query

sparur — Thu, 17 Jun 2010 10:04:06 GMT

hello Venu.

you can auto select certain year when you move between sheet.

create a trigger for every sheet.

goto trigger tab in sheet properties. click Add Action button for on Activate event.

then add action. selection -> select in field. fill field name and field value.

and do these steps on each sheet with different values of year

Filtered Query

Thu, 17 Jun 2010 10:31:22 GMT

You could solve this by creating a different set of data for the objects on the sheet by using SET ANALYSIS. I did a similar thing for an application that had a sheet in it, that overwrites the selections made in other sheets, without changing them.

Your expression would be something like:

=sum({$<Year = {2010}> Sales)

for a sales figure, that only has 2010 data in it, while this is not selected. In my case, I had to make sure to include all months of 2010, so I had to ignore selections made elsewhere in the app like this:

=sum({1<Year = {2010}, Month= > Sales)

Note the $ changing into 1, see help for this. If possible, create a variable that holds the set and apply that to all the expressions, as these sets tend to get quite complex (depending on the number of values in the selection you have to change).

There is an excellent example called "Set Analysis - Comparing Selections.qvw" (which I cannot find at the moment, sorry)

Regards.

Filtered Query

qlik_venu — Thu, 17 Jun 2010 11:03:46 GMT

Hi,

Thanks for the reply.

I was able to create a trigger on Sheet event trigger. I used OnActivatesheet to select the Field value (2010) in the selection. It works well. When i moved to sheet, it triggered and field 2010 was selected.

But when i move to other sheets, where i havent set any triggers, i want to unselect 2010 for the Date field. I was not able to find Clear field option. Instead there were Clear Other Fields and Clear All.

Is thee any way to unselect a particluar field alone.

Thanks,

Venu

Filtered Query

sparur — Thu, 17 Jun 2010 11:17:58 GMT

you don't need to clear selection in year field. set up action on other sheet with different values.

sheet1:

select in field -> field Year -> Value 2008

sheet2:

select in field -> field Year -> Value 2009

And you will be happy 🙂

Filtered Query

qlik_venu — Thu, 17 Jun 2010 13:11:35 GMT

Hi,

Thanks, I am looking for similar to this. I would prefer having the exp filter the current year.

But i have a query.

The following is the expression am using,

Max ({$<PERIOD_ID = {201004}>} If(REPORT_CODE='Y',Sales,Null)). This works good for Apr 2010.

but i need for just the year part of the PERIOD_ID.

KPI_PERIOD_ID contains 201002, 201004, 201007, 201011 , 200901, 200903, 200906( the months might change for every other year).

KPI_Period_ID should be always for the the current year alone.

How can we get this.

Venu

Filtered Query

sparur — Thu, 17 Jun 2010 13:41:20 GMT

can you post your qvw?

Filtered Query

Thu, 17 Jun 2010 13:52:03 GMT

I'm not quiete sure what you want, but is there a way to get an extra field in your dataset that only holds the year (so you can select on YEAR and on PERIOD_ID), this would probably be the easiest way...

You could also use $<PERIOD_ID={2010*}> this is valid according to the QV Reference Manual (Part III, page 353 and up)

Kind regards

Filtered Query

qlik_venu — Fri, 18 Jun 2010 15:00:00 GMT

Hi,

The {2010*} is not working for me. Is there any other means to get this.

Also i am not having the QV Reference Manual. Is it available in the Qlikview website. Can you share the link.

Thanks,

Venu

Filtered Query

Mon, 28 Jun 2010 19:25:00 GMT

Sorry about the late answer.

The QV reference manual can be obtained in the download section of the qlikview website: http://global.qlik.com/download/

You should get this documentation and read about the set analysis.

Kind regards, Luuk