Pick only value for latest date

[akpofureenughwu]

Good day one

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

Location	01/01/2017	02/01/2017	03/01/2017
Area 1	150	100	120
Area 2	100	40	60
Area 3	70	100	150

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

[Anonymous]

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]

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

[Anonymous]

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]

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....

[Anonymous]

then you should post some sample data...

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

[akpofureenughwu]

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

[Anonymous]

frontend or backend solution?

[akpofureenughwu]

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

[akpofureenughwu]

roharoha‌

Good day sir,

Still waiiting for your response sir,

Regards