Need Help In set Analysis for MAx Value

[Anonymous]

Hello!  I need help in my set analysis in my pivot table  to get the max value...get the  latest award Award Value  based on Max date by FY, FQ and Company even if the Qty is 0

Here is my sample data

Date	Company	FY	FQ	Award	Qty
10/26/2014	A	2015	Q1	15	15
10/27/2014	A	2015	Q1	15	200
10/28/2014	A	2015	Q1	30	300
10/29/2014	A	2015	Q1	30	400
10/30/2014	A	2015	Q1	30	0
10/26/2014	B	2015	Q1	20	400
10/27/2014	B	2015	Q1	15	500
10/28/2014	B	2015	Q1	20	600
10/29/2014	B	2015	Q1	45	200
10/30/2014	B	2015	Q1	20	20
1/25/2014	A	2015	Q2	15	50
1/26/2014	A	2015	Q2	30	100
1/27/2014	A	2015	Q2	20	30
1/28/2014	A	2015	Q2	30	20
1/29/2014	A	2015	Q2	80	0
1/30/2014	B	2015	Q2	15	400
1/31/2014	B	2015	Q2	30	500
2/1/2014	B	2015	Q2	20	600
1/25/2014	B	2015	Q2	20	200
1/26/2014	B	2015	Q2	10	20

I tries using the formula  but doesn't work...When I select  FY2015 Q4, only the row with more than  0 qty will show up...I want all awards to show up even if  its 0 qty...

=only({<FY=,FQ=,Company=,Qty=,Date={"$(=MaxString(Date))"}>}Award)

  This is my desired results :

Date	Company	FY	FQ	Award
10/30/2014	A	2015	Q1	30
10/30/2014	B	2015	Q1	20
1/29/2014	A	2015	Q2	80
1/26/2014	B	2015	Q2	10

I hope you can help me on this ...

Tks.

[Anonymous]

I really want to show the Award for the ) qty...But it's ok...this is better.

Thank you for your time.

[sunny_talwar]

No problem

I am glad I was able to help.

Best,

Sunny

[jagan]

Hi,

Try this script

Table:

LOAD AutoNumber(Company & FY & FQ) AS Key,

  Date(Date) AS Date,

     Company,

     FY,

     FQ,

     Award,

     Qty

FROM

[https://community.qlik.com/thread/170074]

(html, codepage is 1252, embedded labels, table is @1);

LatestAward:

LOAD

     Key,

     Date(Max(Date)) AS Date,

     1 AS LatestAward

RESIDENT Table

GROUP By Key;

Now in chart :

Dimensions: Date, Company, FQ, FY

Expression: =Only({<LatestAward={1}>} Award)

Hope this helps you.

Regards,

jagan.