Finding Max by Month

[#1]

Hello All,

I am newbie to Qlikview and need help in getting resolution for below scenario.

I have a table with following structure and data and I need to find latest record by Month.

Date	Month	ID	Name	Sysid
7/10/2015 12:38	Jul-15	8	ABC1	5
7/23/2015 9:12	Jul-15	9	ABC2	6
8/8/2015 19:29	Aug-15	14	LMN	7
9/29/2015 11:34	Sep-15	17	XYZ1	8
9/29/2015 16:20	Sep-15	19	XYZ2	9

Expected Output

Month	Name
Jul-15	ABC2
Aug-15	LMN
Sep-15	XYZ2

I wanted to find maximum by each month based on Date and Sysid fields.

I have gone through few of the posts which uses Max & Aggr functions however I am not able to achieve above expected output.

Could anyone pls help on this?

Regards

[sunny_talwar]

Create a straight table

Dimension

Month

Expression

FirstSortedValue(Name, -Date)

[Anil_Babu_Samineni]

Or May be MaxString(Name)

[CarlosAMonroy]

Hi Nikhil,

If your Month field is not numeric the max function will not work.

You can try using the Date field. First create a straight table and add the following:

Dimension:

Name

Expression:

MonthName(Max(Date))

Hope that helps,

Carlos M

[#5]

Thanks for quick reply however it does not still quite work out the way I expected.

The Month column I am deriving it in the script by converting Date into format as MMM-YYYY so when I have Month as Jul-2015 it does not give me ABC2 and for Sep-2015 it does not give me XYZ2 instead it gives following output.

Jul-2015   ABC1

Jul-2015   ABC2

Aug-2015 LMN

Sep-2015 XYZ1

Sep-2015 XYZ2

If I change the format of my Month field as just MMM then it works as expected however  I need Year also along with Month.

Regards,

Nikhil

[vijetas42]

Hi,

Please create straight table with dimension month and add expression like,aggr(MaxString(Name),Month)

[rahulpawarb]

Hello Nikhil,

Please refer below draft version of script to get the desired result:

Data:
LOAD * INLINE [
Date, Month, ID, Name, Sysid
7/10/2015 12:38, Jul-15, 8, ABC1, 5
7/23/2015 9:12, Jul-15, 9, ABC2, 6
8/8/2015 19:29, Aug-15, 14, LMN, 7
9/29/2015 11:34, Sep-15, 17, XYZ1, 8
9/29/2015 16:20, Sep-15, 19, XYZ2, 9
];
INNER JOIN
LOAD Month,
     MaxString(Name) AS Name
Resident Data
Group By Month;

Also refer the sample application attached herewith.

Hope this will be helpful.

Regards!
Rahul

[sunny_talwar]

When you created Month, did you use MonthStart? If not, then create it like this

Date(MonthStart(Date), 'MMM-YYYY') as Month

[_armoco_]

Hi Nikhil,

The solution of Rahul will work perfectly!

You may also load data from excel instead of using inline!

[muthukumar77]

Hi,

Straight Table:

Dimension: Month

Expression: MaxString(Name)