topic Re: Entries with max Date to be shown in QlikView

Entries with max Date to be shown

Wed, 17 Jun 2015 08:46:08 GMT

Hi Folks,

I have a table like the one below, now I need to show only the entries with the latest Approval Date for each Client, separately.

Actual Fee is an expression.

Current table:

Required table:

Regards,

Dawar

Re: Entries with max Date to be shown

Wed, 17 Jun 2015 08:53:57 GMT

Hi Haimanta

In your expression for [Actual Fee] include some dynamic set analysis that filters the table by date

=Sum({<[Approval Date]={$(=Max([Approval Date]))}>}[Actual Fee])

I sometimes encounter problems when the dates are formatted in a particular way, so you may need some trial and error here. Although it's best to have your dates as numbers and then format them in your tables and charts.

Re: Entries with max Date to be shown

jonathandienst — Wed, 17 Jun 2015 08:54:56 GMT

Straight table

-dimension Client Name

-expressions

-FIrstSortedValue([Engagement Evaluatuion ID], [Approval Date])

-Date(Max([Approval Date]))

-FIrstSortedValue([Actual Fee], [Approval Date])

Re: Entries with max Date to be shown

jonathandienst — Wed, 17 Jun 2015 08:56:38 GMT

This will only return the global maximum date, note the maximum per Client Name.

Re: Entries with max Date to be shown

alexandros17 — Wed, 17 Jun 2015 08:56:46 GMT

An Example

MyTable:
LOAD * Inline [
Customer, Value, Date
A, 100, 05/06/2015
A, 120, 01/03/2015
A, 110, 11/04/2015
B, 210, 27/02/2015
B, 250, 01/06/2015
C, 105, 03/03/2015
C, 370, 17/06/2015
C, 469, 15/01/2015
D, 53, 22/01/2015
D, 78, 03/01/2015
];

Left Join
LOAD Customer, Max(Date) as Date, '1' as FlagMaxDate Resident MyTable Group By Customer;

FinalTable:
NoConcatenate
LOAD * Resident MyTable Where FlagMaxDate='1';
DROP Table MyTable;

Re: Entries with max Date to be shown

jduarte12 — Wed, 17 Jun 2015 08:57:48 GMT

Hello.

I would try to do the following:

1- "Client" is your dimension;

2- Use set analysis to compute your "actual fee" expression. Someting like:

actual_fee = Sum({<approval_date={"$(=max(approval_date))"}>} parameter)

Best regards,

João Duarte

Re: Entries with max Date to be shown

fvelascog72 — Wed, 17 Jun 2015 08:59:28 GMT

Hi,

Try this:

DATA:

LOAD

[Client Name],

[Engagement Evaluation ID],

Date#([Approval Date],'DD-MMM-YY') as "Approval Date"

Inline [

Client Name, Engagement Evaluation ID, Approval Date

1FB SUPPORT SERVICIES PRIVATE LIMITED, 5046578, 11-dic-13

1FB SUPPORT SERVICIES PRIVATE LIMITED, 5063151, 7-ene-14

2WIRE DEVEPOLMENT CENTER (INDIA) PRIVATE LIMITED, 5321943, 17-jul-14

2WIRE DEVEPOLMENT CENTER (INDIA) PRIVATE LIMITED, 4488361, 16-abr-12

];

Right Join (DATA)

LOAD

[Client Name],

Date(Max([Approval Date]),'DD-MMM-YY') as "Approval Date"

Resident DATA

Group by [Client Name];

Re: Entries with max Date to be shown

jonathandienst — Wed, 17 Jun 2015 09:07:34 GMT

João Nuno Duarte wrote:

Hello.

I would try to do the following:

1- "Client" is your dimension;

2- Use set analysis to compute your "actual fee" expression. Someting like:

actual_fee = Sum({<approval_date={"$(=max(approval_date))"}>} parameter)

Best regards,

João Duarte

See my comment above

Re: Entries with max Date to be shown

Wed, 17 Jun 2015 09:25:34 GMT

As mentioned by Jonathan,

I already tried doing changes in the expression before like
=Sum({<[Approval Date]={$(=Max([Approval Date]))}>}[Actual Fee])

but only the global latest Approval Date entries were only displayed

Re: Entries with max Date to be shown

Wed, 17 Jun 2015 10:33:28 GMT

Hi Jonathan,

This is not giving me the Engagement Evaluation ID as per latest Approval Date but instead that of the first Approval Date

Re: Entries with max Date to be shown

Wed, 17 Jun 2015 10:35:25 GMT

Maybe.. I need to try something in the script level by doing a join