Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

akpofureenughwu
Contributor II

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Pick only value for latest date

Highest sales value per day? Across all days you want the highest value? May be this

Max(Aggr(Sum(Sales), Date))

21 Replies
roharoha
Valued Contributor III

Re: Pick only value for latest date

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();

akpofureenughwu
Contributor II

Re: Pick only value for latest date

roharoha

The script you suggest above worked... Can I display the above result (the value based on the latest date) and the highest value in the given time frame?....

Thank you sir

roharoha
Valued Contributor III

Re: Pick only value for latest date

add at the end:

FINALFINALFINALTABLE:

Load

    firstsortedvalue([Location],-Value) as [Location] ,

    firstsortedvalue(Date(Num#(Date)),-Value) as Date,

    firstsortedvalue(Month(Num#(Date)),-Value) as Month,

    firstsortedvalue('Q' & Ceil(Month(Num#(Date))/3),-Value) as Quarter,

    firstsortedvalue(MonthName(Num#(Date)),-Value) as MonthYear,

    firstsortedvalue(Year(Num#(Date)),-Value) as Year,

    firstsortedvalue(Day(Num#(Date)),-Value) as Day,

    max(Value) as Value

    resident FINALFINALTABLE;

drop Table FINALFINALTABLE;

akpofureenughwu
Contributor II

Re: Pick only value for latest date

I included the latest script you suggested ....

FINALFINALFINALTABLE:

Load

    firstsortedvalue([Location],-Value) as [Location] ,

    firstsortedvalue(Date(Num#(Date)),-Value) as Date,

    firstsortedvalue(Month(Num#(Date)),-Value) as Month,

    firstsortedvalue('Q' & Ceil(Month(Num#(Date))/3),-Value) as Quarter,

    firstsortedvalue(MonthName(Num#(Date)),-Value) as MonthYear,

    firstsortedvalue(Year(Num#(Date)),-Value) as Year,

    firstsortedvalue(Day(Num#(Date)),-Value) as Day,

    max(Value) as Value

    resident FINALFINALTABLE;

drop Table FINALFINALTABLE;

I practically had not data to visualized....

roharoha
Valued Contributor III

Re: Pick only value for latest date

then you should post some sample data...

Do you need only 1 max date and 1 max value?

akpofureenughwu
Contributor II

Re: Pick only value for latest date

Here is the sample data...

in the app I am building , I'm to visualize the sales of each location at the latest date ( use of bar chart ) as well as

the maximum sales of  the available period  say   in day 7  Area 1 sold 6,000 and area 2 sold 7,000.. the sum is 13,000 .. If this is the peak then I want to visualize this figure

.. ( in the sample file from January to April 2017)

Regards

roharoha
Valued Contributor III

Re: Pick only value for latest date

frontend or backend solution?

akpofureenughwu
Contributor II

Re: Pick only value for latest date

I would prefer a frontend solution since this will allow me to load all the available data

akpofureenughwu
Contributor II

Re: Pick only value for latest date

roharoha

Good day sir,

Still waiiting for your response sir,

Regards