Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
akpofureenughwu
Creator III
Creator III

Pick only value for latest date

Good day one

I have a set of data I need to pick only the latest data

Location01/01/201702/01/201703/01/2017
Area 1150100120
Area 21004060
Area 370100150

I used the crosstable function to resolve my data

Something like this

At the backend

Fox:

CrossTable (Date, Value, 1)

LOAD [Location],

    [42736],

    [42737],

    [42738]

from DAILY Sales.xlsx]

(ooxml, embedded labels, table is Sample);

FinalTable:

Load [Location],

  Date(Num#(Date)) as Date,
Month(Num#(Date)) as Month,
'Q' & Ceil(Month(Num#(Date))/3) as Quarter,
MonthName(Num#(Date)) as MonthYear,
Year(Num#(Date)) as Year,
Day(Num#(Date)) as Day,
Value

Resident Fox;

Drop Table Fox;

All I need is how I can only display value based on latest date

21 Replies
Anonymous
Not applicable

btw, please don't rush me... as long as I'm at work I won't answer...

akpofureenughwu
Creator III
Creator III
Author

roharoha

Thank yo sir... sorry for the panic message man.. Just one more thing (please take your time.. I'm not rushing you bro)..

How can I display the highest value of (Max(Aggr(sum(Valuefield_),Datefield_))) for the given period,

My client would love to see the peak sales value for the given period.. (more like Peak Sales YTD)

Thank you for your time and effort. I truly appreciate

Regards

Anonymous
Not applicable

Hi, I suppose that your period starts at January 1st...

Max(Aggr(sum({<Datefield_={"=InYearToDate(Datefield_,Today(),0)"}  >} Valuefield_),Datefield_))

Anonymous
Not applicable

or like that:

Max(Aggr(sum({<Datefield_={">=$(=Yearstart(Today()))"}  >} Valuefield_),Datefield_))

Anonymous
Not applicable

is your question answered or not?

akpofureenughwu
Creator III
Creator III
Author

roharoha

My answer has been answered, I just got a new requirement from my client, 

From the script you suggest to me :

Fox:

CrossTable (Date, Value, 1)

LOAD [Location],

    [42736],

    [42737],

    [42738]

from DAILY Sales.xlsx]

(ooxml, embedded labels, table is Sample);

FinalTable:

Load [Location],

  Date(Num#(Date)) as Date,
Month(Num#(Date)) as Month,
'Q' & Ceil(Month(Num#(Date))/3) as Quarter,
MonthName(Num#(Date)) as MonthYear,
Year(Num#(Date)) as Year,
Day(Num#(Date)) as Day,
Value

Resident Fox;

Drop Table Fox;

TEMP: LOAD max(Date) as MAXDATE resident FinalTable;

let varmaxdate = peek('MAXDATE');

drop Table TEMP;

Noconcatenate

FINALFINALTABLE:

LOAD * resident FinalTable

where Date=$(varmaxdate);

drop table FinalTable;

let varmaxdate=null();

Is it possible to replicate this script in the front end? so that at the back end, all I have is

Fox:

CrossTable (Date, Value, 1)

LOAD [Location],

    [42736],

    [42737],

    [42738]

from DAILY Sales.xlsx]

(ooxml, embedded labels, table is Sample);

FinalTable:

Load [Location],

  Date(Num#(Date)) as Date,
Month(Num#(Date)) as Month,
'Q' & Ceil(Month(Num#(Date))/3) as Quarter,
MonthName(Num#(Date)) as MonthYear,
Year(Num#(Date)) as Year,
Day(Num#(Date)) as Day,
Value

Resident Fox;

Drop Table Fox;

Thank you for your unprecedented support

Regards

akpofureenughwu
Creator III
Creator III
Author

For clarity,

With this script...

Fox:

CrossTable (Date, Value, 1)

LOAD [Location],

    [42736],

    [42737],

    [42738]

from DAILY Sales.xlsx]

(ooxml, embedded labels, table is Sample);

FinalTable:

Load [Location],

  Date(Num#(Date)) as Date,
Month(Num#(Date)) as Month,
'Q' & Ceil(Month(Num#(Date))/3) as Quarter,
MonthName(Num#(Date)) as MonthYear,
Year(Num#(Date)) as Year,
Day(Num#(Date)) as Day,
Value

Resident Fox;

Drop Table Fox;

I can sort out the latest date with this expression

' maxstring(Date)  ' (from the front end)

Is it possible to get the latest value for the front end?...

Anonymous
Not applicable

if you like to get the maximum date overall, use

max(total date)

in frontend, that should be all

akpofureenughwu
Creator III
Creator III
Author

Thank you sir, but that doesn;t answer my question...

Take this for example...

DateLocationSalesRefund
01/01/2017Area 110020
01/01/2017Area 215030
02/01/2017Area 15010
02/01/2017Area 21600

My client need to see just the latest date value ... (from the table  above ) is Area 1 .... 50, Area 2 160 ( for sales)

I need this done from the front end sir

Thank you roharoha stalwar1       

sunny_talwar

May be this

FirstSortedValue(Sales, -Date)

or

FirstSortedValue(Aggr(Sum(Sales), Date, Location), -Aggr(Date, Date, Location))