topic Max date with Set Analysis in QlikView

Max date with Set Analysis

Tue, 02 Feb 2016 20:35:33 GMT

Hi folks!
I have a question that is apparently simple because've seen several topics dealing with the subject, but none of the tips posted over there did not attend to my case.

The following is:

I have this table

I need a way to be shown only the values of the major dates in a pivot table

Name, date, value

A, 31/01/2016, 20

B, 17/01/2016, 10

c, 31/01/2016, 35

D, 31/01/2016, 20

The problem I'm having is that I can or that

Name, date, value

A, 31/01/2016, 20

B, 17/01/2016, 10

c, 31/01/2016, 35

D, 31/01/2016, 40

When use:

=sum({$<[FORMULARIO] = {'M*'},[COLUNA] = {'Em and*'},

[CLASSIFICAÇÃO] -= {'*Plantão*', '*Mutirão*', '*Júri*', '*Auxílio*'}, [date]={"=$(=max([date]))"} >} value)

or with this:

Name, date, value

A, 31/01/2016, 00

B, 17/01/2016, 10

C, 31/01/2016, 35

D, 31/01/2016, 20

When use:

=sum({$<[FORMULARIO] = {'M*'},[COLUNA] = {'Em and*'},

[CLASSIFICAÇÃO] -= {'*Plantão*', '*Mutirão*', '*Júri*', '*Auxílio*'}, [date]={"$(=max([date]))"} >} value)

The problem lies precisely in the names A and D.

Does anyone have any ideas?

Thanks in advance for suggestions.

Re: Max date with Set Analysis

sunny_talwar — Tue, 02 Feb 2016 21:13:18 GMT

for date set analysis try with Date() function to match the format on LHS and RHS:

[date]={"$(=Date(Max([date]), 'DD/MM/YYYY'))"}

Re: Max date with Set Analysis

Wed, 03 Feb 2016 12:31:15 GMT

First, thanks for the help.
Unfortunately it does not. All numbers were at 0 (zero).
Had tried this syntax.

Improving my question, I have the following table:

Name, date, value

A, 31/01/2016, 20

A, 14/01/2016, 00

B, 28/01/2016, 10

B, 15/01/2016, 05

C, 12/01/2016, 10

C, 31/01/2016, 15

D, 31/01/2016, 20

I would get this table as a result:

A, 31/01/2016, 20

B, 28/01/2016, 10

C, 31/01/2016, 15

D, 31/01/2016, 40

Still waiting for help.
Thanks again!

Re: Max date with Set Analysis

sunny_talwar — Wed, 03 Feb 2016 17:53:23 GMT

I suggest creating flag in the script for the max date of each month and then use that in your set analysis.

Script:

Table:

LOAD * Inline [

Name, date, value

A, 31/01/2016, 20

A, 14/01/2016, 00

B, 28/01/2016, 10

B, 15/01/2016, 05

C, 12/01/2016, 10

C, 31/01/2016, 15

D, 31/01/2016, 20

];

Join(Table)

LOAD Name,

Max(date) as date,

1 as MaxFlag

Resident Table

Group By Name;

Re: Max date with Set Analysis

Wed, 03 Feb 2016 19:10:22 GMT

Again, thanks for the help, Sunny T, but failed.

My table has 800,000 records. There are 200 different names with dates ranging from January / 2011 to January / 2016.

When you apply your suggestion, the return is only the max 200 dates. When you apply filters, for example, that the highest values of May / 2014, nothing appears as the greatest dates, because the max dates are recorded as in January / 2016.

Re: Max date with Set Analysis

sunny_talwar — Wed, 03 Feb 2016 19:26:02 GMT

For it to be dynamic, you would need a front end solution. Try this:

=Sum(Aggr(NODISTINCT If(date = Max(Total <Name> date), value), Name, date))

Re: Max date with Set Analysis

Wed, 03 Feb 2016 19:31:06 GMT

Sunny T, we can!

I made a small change in your suggestion and it worked.

Join (Table)

LOAD Name,

Max (date) the date,

1 the MaxFlag

Resident Table

Group By Name, month;

Include the month in the group.

Thank you so much!

Re: Max date with Set Analysis

sunny_talwar — Wed, 03 Feb 2016 19:35:36 GMT

Awesome! I think I might have misread your requirement.

I am glad you were able to figure it out