topic Re: Aggr expression to solve dynamic Date selection in QlikView

Aggr expression to solve dynamic Date selection

Mon, 12 Nov 2012 18:32:15 GMT

Hi,

I need help with the following problem.

I have a table with the following format:

What we need is the following:

- Get the max DATE for each Field2, -- Aggr (Max (Date), Field2).

- The result of the above, do a count distributed by Field3.

For Example:

Field3 Count

Category1 3

Category2 1

Category3 2

Dimension: Field3

Expression: Aggr(Max(Fecha), Field2) -- and a count by Field3.

NOTE: I need resolve this in object level because the field DATE is select dynamic by the user.

I attach a QlikView document.

Can anyone help?

Thank you very much!

Guzmán

Re: Aggr expression to solve dynamic Date selection

Gysbert_Wassenaar — Mon, 12 Nov 2012 20:20:19 GMT

It's not clear to me what you want exactly. You can't get the max dates and the counts per category in one table. You'll need two tables.

I'm guessing that a user selects one or more dates and wants to see the max dates of the selected dates per Field2 and the count per category where the date is smaller or equal to the max date of the selected dates. See the attached qvw file.

If that's not what you need you'll have to explain what you need. You could use excel to make example tables the way you want the result to look.

Re: Aggr expression to solve dynamic Date selection

Tue, 13 Nov 2012 01:44:26 GMT

Gysbert thank you very much for the quick response.

I adjusted based on your qvw, and then explain a particular case.

In the previous image, select the first 4 values from Table1. In Table2 I have records filtered by that selection.

As can you see, each Field2 value may correspond to several Date values and Field3 values.

I need only count Field2 values for which Date value is maximum.

In the example:

Field2 Date

123456 - 06/10/2012

456123 - 09/10/2012

Then link these values with the corresponding Field3:

Date Field2 Field3

06/10/2012 - 123456 - Category3

09/10/2012 - 456123 - Category1

And finally get a count by Field3:

Field3 Count

Category1 - 1

Category3 - 1

Is a count of Field2 distributed by Field3, but taking only the max Date value for each Field2 value and its corresponding Field3 value.

It's a little hard to explain, I hope I have helped. I attached the qvw file and a Excel with some examples.

Thank you very much

Guzmán