Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Can we use the max function in load scipt in Qliksense

Hi ,

I want to use max function at backend to find the maximum report date coming from multiple bases.

Is it possible?

11 Replies
Highlighted
Champion III
Champion III

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;

Highlighted
Partner
Partner

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.

Highlighted
Champion III
Champion III

before max date means? 

can you share sample 3,4 lines of data through inline and what is your expected result?

Highlighted
Partner
Partner

Yes,

So if my max date is '31-Jan-2018' my field maxdate should have the value '31-Dec-2017'

Highlighted
Champion III
Champion III

you want monthend ?

=monthend(today()-1)

Highlighted
Partner
Partner

Hi Chanty,

But I want to find the maximum date from the base , Today() will give me the present date.

Highlighted
Partner
Partner

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

Highlighted

Could you consider the same dates and the desired output your looking for ? so that will help us to understand better

Highlighted
Partner
Partner

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