topic Finding Max by Month in QlikView

Finding Max by Month

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Finding Max by Month

sunny_talwar — Mon, 20 Mar 2017 16:04:35 GMT

Create a straight table

Dimension

Month

Expression

FirstSortedValue(Name, -Date)

Re: Finding Max by Month

Anil_Babu_Samineni — Mon, 20 Mar 2017 16:08:53 GMT

Or May be MaxString(Name)

Re: Finding Max by Month

CarlosAMonroy — Mon, 20 Mar 2017 19:15:36 GMT

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

Re: Finding Max by Month

Tue, 21 Mar 2017 06:28:52 GMT

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

Re: Finding Max by Month

vijetas42 — Tue, 21 Mar 2017 07:08:52 GMT

Hi,

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

Re: Finding Max by Month

rahulpawarb — Tue, 21 Mar 2017 07:21:30 GMT

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

Re: Finding Max by Month

sunny_talwar — Tue, 21 Mar 2017 08:25:56 GMT

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

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

Re: Finding Max by Month

_armoco_ — Tue, 21 Mar 2017 08:55:31 GMT

Hi Nikhil,

The solution of Rahul will work perfectly!

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

Re: Finding Max by Month

muthukumar77 — Tue, 21 Mar 2017 09:43:27 GMT

Hi,

Straight Table:

Dimension: Month

Expression: MaxString(Name)

Re: Finding Max by Month

sunny_talwar — Tue, 21 Mar 2017 09:54:14 GMT

Script

Table:

LOAD *,

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

LOAD * INLINE [

Date, ID, Name, Sysid

7/10/2015 12:38, 8, ABC1, 5

7/23/2015 9:12, 9, ABC2, 6

8/8/2015 19:29, 14, LMN, 7

9/29/2015 11:34, 17, XYZ1, 8

9/29/2015 16:20, 19, XYZ2, 9

];

Re: Finding Max by Month

Wed, 22 Mar 2017 06:12:32 GMT

Thanks Sunny. By using MonthStart while loading has worked as expected.

Thanks others as well who have responded with their suggestions.

Regards,

Nikhil