Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
btw, please don't rush me... as long as I'm at work I won't answer...
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
Hi, I suppose that your period starts at January 1st...
Max(Aggr(sum({<Datefield_={"=InYearToDate(Datefield_,Today(),0)"} >} Valuefield_),Datefield_))
or like that:
Max(Aggr(sum({<Datefield_={">=$(=Yearstart(Today()))"} >} Valuefield_),Datefield_))
is your question answered or not?
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
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?...
if you like to get the maximum date overall, use
max(total date)
in frontend, that should be all
Thank you sir, but that doesn;t answer my question...
Take this for example...
Date | Location | Sales | Refund |
---|---|---|---|
01/01/2017 | Area 1 | 100 | 20 |
01/01/2017 | Area 2 | 150 | 30 |
02/01/2017 | Area 1 | 50 | 10 |
02/01/2017 | Area 2 | 160 | 0 |
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
May be this
FirstSortedValue(Sales, -Date)
or
FirstSortedValue(Aggr(Sum(Sales), Date, Location), -Aggr(Date, Date, Location))