Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
DS14
Partner - Contributor III
Partner - Contributor III

pull max(Date) from each month

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

Labels (3)
1 Solution

Accepted Solutions
bhargav_bhat
Creator II
Creator II

Hi Deepak,

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

Load 

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

View solution in original post

10 Replies
Vince_CH
Creator III
Creator III

You may with following?
=Aggr(Max(date),Month)
bhargav_bhat
Creator II
Creator II

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

Load 

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

DS14
Partner - Contributor III
Partner - Contributor III
Author

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.

Please suggest.

bhargav_bhat
Creator II
Creator II

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

kaanerisen
Creator III
Creator III

Hi deepaksingh,

Vince's expression is valid actually.

Sample_Data:
load
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
];

Untitled.png

 

DS14
Partner - Contributor III
Partner - Contributor III
Author

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.

bhargav_bhat
Creator II
Creator II

Hi Deepak,

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

Load 

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

DS14
Partner - Contributor III
Partner - Contributor III
Author

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.

bhargav_bhat
Creator II
Creator II

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


inline_table:
load
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:
Load
Month,
Max(Date) as Date
resident inline_table
group by Month;

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

Drop table Max_table;

 

Capture.PNG

 

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

 

Regards,

Bhargav