Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

siddharthsoam
Contributor

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
sureshqv
Esteemed Contributor III

Re: Can we use the max function in load scipt in Qliksense

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;

siddharthsoam
Contributor

Re: Can we use the max function in load scipt in Qliksense

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.

sureshqv
Esteemed Contributor III

Re: Can we use the max function in load scipt in Qliksense

before max date means? 

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

siddharthsoam
Contributor

Re: Can we use the max function in load scipt in Qliksense

Yes,

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

sureshqv
Esteemed Contributor III

Re: Can we use the max function in load scipt in Qliksense

you want monthend ?

=monthend(today()-1)

siddharthsoam
Contributor

Re: Can we use the max function in load scipt in Qliksense

Hi Chanty,

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

OmarBenSalem
Esteemed Contributor

Re: Can we use the max function in load scipt in Qliksense

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

Re: Can we use the max function in load scipt in Qliksense

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

siddharthsoam
Contributor

Re: Can we use the max function in load scipt in Qliksense

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

Community Browser