Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How can I 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
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
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.
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
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
];
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.
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
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.
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;
the expression is Max({<maxflag={1}>}CP)
Regards,
Bhargav