topic How Do I Filter Pivot Table Automatically Based on Most Recent Date in QlikView

How Do I Filter Pivot Table Automatically Based on Most Recent Date

Thu, 03 May 2012 21:51:38 GMT

HI,

Appologies in advance! I am new to Qlikview this week and am not a developer.

I have a rather large table which is essentially a concatenation of data from a series of different dates. (Think of warehouse inventory from different dates over time) The date that the "copy" of the data was appended is in a field called the "Posted_Date".

Posted_Date	Item_Name	Data1	Data2	ETC...
3/10/2012	Apples	x	x	x
3/10/2012	Bananas	x	x	x
3/10/2012	Carrots	x	x	x
2/18/2012	Apples	x	x	x
2/18/2012	Bananas	x	x	x
2/18/2012	Carrots	x	x	x
1/07/2012	Apples	x	x	x
1/07/2012	Bananas	x	x	x
1/07/2012	Carrots	x	x	x

I would like to accomplish the following items:

Display the data in a pivot table (got that one )
Be able to filter the data based on a SINGLE date (I can do this if I trust the user to select only one date from a list box ... but I don't )
Therefore, what I would really like to do is...
- Allow the user to pick a SINGLE date from the list of possible Posted_Dates as the date for the pivot table.
- Enable the system to use the most recent date if a date was not selected by the use

I have been trying to get my head around AGGR and ONLY functions for a couple of hours now and only managed to give myself a headache. (Did I mention that I am not a developer type)

I created a sample qvw in hopes that this is easy for someone who knows how to write functions.

I have a number of charts which show inventory levels over time. I plan on "reusing" this code to create related charts which always show the most current data. (Some of my charts are too complicated to use the sort and display only most recent trick).

Any and all help is greatly appreciated.

Thanks

PeggySue

Re: How Do I Filter Pivot Table Automatically Based on Most Recent Date

Fri, 04 May 2012 12:10:52 GMT

He PeggySue,

There is an option in the listbox:always one selected value. And you could also use a trigger on open document to select the latest date.

See the attached file.

Hopes this helps.

gr.

Frank

How Do I Filter Pivot Table Automatically Based on Most Recent Date

Fri, 04 May 2012 12:51:33 GMT

So, the checkbox to limit the selection to one response is a great feature.

However, I don't believe that it would work in my situation.

There will be a number of items on the page but I want to only (and always) filter the pivot table limiting it to one date.

Imagine a chart which displays data from the entire time series but when a user wants to export out the data or look at the detail, they are only allowed to look at a particular date.

Thanks PeggySue

Re: How Do I Filter Pivot Table Automatically Based on Most Recent Date

Fri, 04 May 2012 13:07:03 GMT

Ok, now I understand what you want.

You can solve this by adding an if statement in your pivot table expression:

if(Posted_Date=Selected_Date, Count(Department))

Selected_Date is a variable with the function =max(Posted_Date).

And then check the option supress when value is null for the dimension Posted_Date.

See the attached file.