Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Partner
Partner

pull max(Date) from each month

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

1 Solution

Accepted Solutions
bhargav_bhat
Contributor II

Re: pull max(Date) from each month

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

10 Replies
Vince_CH
Contributor II

Re: pull max(Date) from each month

You may with following?
=Aggr(Max(date),Month)
bhargav_bhat
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

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

Partner
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.

Please suggest.

bhargav_bhat
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

kaanerisen
Contributor III

Re: pull max(Date) from each month

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

 

Partner
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.

bhargav_bhat
Contributor II

Re: pull max(Date) from each month

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

Partner
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.

bhargav_bhat
Contributor II

Re: pull max(Date) from each month

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