Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
I want to use max function at backend to find the maximum report date coming from multiple bases.
Is it possible?
you can
table:
LOAD Project,
Issue,
[Issue ID],
[Issue Update ID],
[Issue Update Comment],
[Date of Update],
[Date of Update]&'_'&Project as _key
FROM
D:\QlikView\AXS\data\test.xlsx
(ooxml, embedded labels, table is Feuil1);
Mytable:
Load Project as ID,
max(date([Date of Update],'DD/MM/YYYY')) as maxdate
Resident table
group by Project;
Hi Chanty,
Thanks for the answer ...
I am trying to find a month before my maxdate.
Trying to use the following expression-
max(date(addmonths(Max(Date#(reportdate,'DD-MMM-YYYY')),-1),'DD-MMM-YYYY')) as maxdate,
But unable to ..
Appreciate your help in this.
before max date means?
can you share sample 3,4 lines of data through inline and what is your expected result?
Yes,
So if my max date is '31-Jan-2018' my field maxdate should have the value '31-Dec-2017'
you want monthend ?
=monthend(today()-1)
Hi Chanty,
But I want to find the maximum date from the base , Today() will give me the present date.
let's assume ur have this table:
loac a,b,c, date from source;
create ur monthdate field :
table:
loac a,b,c, date , Month(date)&' '&year(date) as MonthYear from source;
//now load this new MonthYear field in a resident table:
Months:
load distinct MonthYear resident table order by MonthYear ascend;
//now, let's see how many rows there is in this table:
vNbRows=NoOfRows('Months');
// and now let's peek the previous to last month (=the previous to last row)
vPrevMonth=peek('Months',$(vNbRows)-1,'MonthYear');
and there u have it
Hope this helps
Could you consider the same dates and the desired output your looking for ? so that will help us to understand better
I have this reportdate column in my base
if("Month"='Oct' and "Fiscal Year"='2017','31-Oct-2017', if("Month"='Sep' and "Fiscal Year"='2017','30-Sep-2017',if("Month"='Mar' and "Fiscal Year"='2016','31-Mar-2017',if("Month"='Aug' and "Fiscal Year"='2017','31-Aug-2017', if("Month"='Nov' and "Fiscal Year"='2017','30-Nov-2017' ,if("Month"='Dec' and "Fiscal Year"='2017','31-Dec-2017',if("Month"='Jan' and "Fiscal Year"='2017','31-Jan-2018'))))))) as reportdate
The most recent reportdate i.e.- 31-Jan-2018
I want an expression which can return a month prior to the most recent date i.e. -31-Dec-2017