topic Re: Need Help In set Analysis for MAx Value in QlikView

Need Help In set Analysis for MAx Value

Anonymous — Thu, 25 Jun 2015 18:11:11 GMT

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.

Re: Need Help In set Analysis for MAx Value

sunny_talwar — Thu, 25 Jun 2015 18:22:13 GMT

Try this expression: =Aggr(FirstSortedValue(Award, -Date), Company, FQ)

Dimension: Date, Company, FY, FQ

Output:

Re: Need Help In set Analysis for MAx Value

sunny_talwar — Thu, 25 Jun 2015 18:27:18 GMT

Newer version, disregard the previous post

Expression1: =FirstSortedValue(Date, -Date)

Expression2: =FirstSortedValue(Award, -Date)

Dimensions: Company, FQ, FY

Attaching the qvw for reference.

Best,

Sunny

Re: Need Help In set Analysis for MAx Value

sunny_talwar — Thu, 25 Jun 2015 18:28:56 GMT

Also, just to make sure I am understanding it correct. In the below image the circled part is the one you want and not the yellowed part, right?

Re: Need Help In set Analysis for MAx Value

Anonymous — Thu, 25 Jun 2015 18:53:47 GMT

HI! Sunindia,

Thank you for your prompt response... Sorry for the confusion but I want the results in yellow... However,I actually made a typo in my sample table...

It's supposed to be like this

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	200
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/25/2014	B	2015	Q2	15	400
1/26/2014	B	2015	Q2	30	500
1/27/2014	B	2015	Q2	20	600
1/28/2014	B	2015	Q2	20	200
1/29/2014	B	2015	Q2	10	20

This is the desired results :...Thus, should be only 1 date per FY, FQ and Award...

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

Tks.

Re: Need Help In set Analysis for MAx Value

sunny_talwar — Thu, 25 Jun 2015 18:57:41 GMT

Output:

Expression1: =FirstSortedValue(Date, -Date)

Expression2: =FirstSortedValue(Award, -Date)

Expression3: =FirstSortedValue(Qty, -Date)

Dimensions: Company, FQ, FY

HTH

Best,

Sunny

Re: Need Help In set Analysis for MAx Value

Anonymous — Thu, 25 Jun 2015 19:16:00 GMT

Hi! This one looks better...However, I noticed for the 0 Qty , it creates a new row f "Unknown" Company...Is there anyway, to get rid of these?

Tks.

Re: Need Help In set Analysis for MAx Value

sunny_talwar — Thu, 25 Jun 2015 19:19:04 GMT

Hi! This one looks better...However, I noticed for the 0 Qty , it creates a new row f "Unknown" Company...Is there anyway, to get rid of these?

Not sure what you mean here?

Re: Need Help In set Analysis for MAx Value

Anonymous — Thu, 25 Jun 2015 19:29:56 GMT

Oh Ok...I have fixed the Unknown ...It's bec...I have added the first sorted value for Company... I took it out and its gone......But the Award for the 0 qty doesn't show up...Thus, only 1 row showed up (with more than 1 qty) for each quarter.

Tks.

Re: Need Help In set Analysis for MAx Value

sunny_talwar — Thu, 25 Jun 2015 19:35:58 GMT

So all is working now then?

Re: Need Help In set Analysis for MAx Value

Anonymous — Thu, 25 Jun 2015 20:19:31 GMT

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

Thank you for your time.

Re: Need Help In set Analysis for MAx Value

sunny_talwar — Thu, 25 Jun 2015 20:22:19 GMT

No problem

I am glad I was able to help.

Best,

Sunny

Re: Need Help In set Analysis for MAx Value

jagan — Fri, 26 Jun 2015 07:13:35 GMT

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.