# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Partner

## pull max(Date) from each month

How can I pull max (date) from each month.

Labels (4)

• ### max date

1 Solution

Accepted Solutions
Contributor II

## Re: pull max(Date) from each month

Hi Deepak,

Create a flag in the script which indicates the latest month as follows

Month,

max(date) as Date,

1 as max_flag

Resident Tablename

group by  Month;

Do a left join based on Month and Date column with the main table

now in line chart add the below expression

Dimension :Month

Measure: max({<max_flag={1}>}Cost Price )

HTH

Bhargav

10 Replies
Contributor II

## Re: pull max(Date) from each month

You may with following？
=Aggr(Max(date),Month)
Contributor II

## Re: pull max(Date) from each month

Hi Deepak ,

Getting Max(Date) at Script level

If you want to find max(date) in Script  First load the main table and do a resident load of the main table with  a group by in the script as follows

Month,

max(date) as MaxDate

Resident Tablename

group by  Month;

and later do a left join with the main table

Getting Max(date) at front end or  expression

Aggr(max(date),month)

Regards,

Bhargav

Partner

## Re: pull max(Date) from each month

Thanks Bhargav,

For quick responce, the method for backend is working fine but on frontend, the expression(Aggr(max(date),month)) is fetching all the dates for the month not the max date present in particular month.

Contributor II

## Re: pull max(Date) from each month

Hi Deepak,

Can you tell me in which chart are you using the expression and what are the dimensions and measures are there along with it

Regards,

Bhargav

Contributor III

## Re: pull max(Date) from each month

Hi deepaksingh,

Vince's expression is valid actually.

Sample_Data:
Date(DATE) as DATE,
Month(DATE) as MONTH,
VALUE
Inline [
DATE,VALUE
1.01.2018,23
3.01.2018,12
17.01.2018,23
4.02.2018,43
13.02.2018,54
15.03.2018,13
16.04.2018,44
27.04.2018,51
];

Partner

## Re: pull max(Date) from each month

I have to show max cost price for the month in line chart depending upon max date in in that month.

Dimension is month and the measure is on cost price .

For example:

Date,                    CP
12/05/2018,      60
23/05/2018,      70
26/04/2018,      40
28/04/2018,     45
16/07/2018,     60
18/07/2018,     65
14/10/2018,     80
16/10/2018      85

I need the output of cost price for the max date of particular month. like for may it should be 70 based on max date and for April 45, for July 65 and for October 85.

Contributor II

## Re: pull max(Date) from each month

Hi Deepak,

Create a flag in the script which indicates the latest month as follows

Month,

max(date) as Date,

1 as max_flag

Resident Tablename

group by  Month;

Do a left join based on Month and Date column with the main table

now in line chart add the below expression

Dimension :Month

Measure: max({<max_flag={1}>}Cost Price )

HTH

Bhargav

Partner

## Re: pull max(Date) from each month

As per your example data i need the output value based on max date, on 13th feb the value is 54 I need, But in your output the value 43. On 27th April'18  the value 51. But in your output the value 44.

Contributor II

## Re: pull max(Date) from each month

Based on your sample data I was able to get the requried result

inline_table:
DAte#(Date,'DD/MM/YYYY') AS Date,
MONTH(DAte#(Date,'DD/MM/YYYY')) AS Month,
CP
Inline
[
Date,CP
12/05/2018,60
23/05/2018,70
26/04/2018,40
28/04/2018,45
16/07/2018,60
18/07/2018,65
14/10/2018,80
16/10/2018,85
];

NoConcatenate
Max_table:
Month,
Max(Date) as Date
resident inline_table
group by Month;

Left join(inline_table)
*,
if(len(Date)>0,1,0) as maxflag
resident Max_table;

Drop table Max_table;

the expression is Max({<maxflag={1}>}CP)

Regards,

Bhargav