Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
I really want to show the Award for the ) qty...But it's ok...this is better.
Thank you for your time.
No problem
I am glad I was able to help.
Best,
Sunny
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.