Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
Not applicable

Max date with Set Analysis

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.

1 Solution

Accepted Solutions
Highlighted
Not applicable

Re: Max date with Set Analysis

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!

View solution in original post

7 Replies
Highlighted

Re: Max date with Set Analysis

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

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

Highlighted
Not applicable

Re: Max date with Set Analysis

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

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!

Highlighted

Re: Max date with Set Analysis

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

D, 31/01/2016, 20

];

Join(Table)

LOAD Name,

  Max(date) as date,

  1 as MaxFlag

Resident Table

Group By Name;

Capture.PNG

Highlighted
Not applicable

Re: Max date with Set Analysis

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.
Highlighted

Re: Max date with Set Analysis

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))


Capture.PNG

Highlighted
Not applicable

Re: Max date with Set Analysis

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!

View solution in original post

Re: Max date with Set Analysis

Awesome! I think I might have misread your requirement.

I am glad you were able to figure it out