Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
How to achieve the second table, I need to show both in the dashboard.
T Number | Date | m1 | m2 |
0003 CM 1 | 17/08/2015 | 0 | 0 |
4 CM 1 | 22/01/2016 | 0 | 0 |
5 CM 1 | 19/02/2016 | 0 | 3,502 |
6 CM 1 | 19/07/2016 | 0 | 3,809 |
7 CM 1 | 19/09/2016 | 0 | 5,350 |
8 CM 1 | 04/10/2016 | 0 | 5,694 |
9 CM 1 | 19/10/2016 | 0 | 5,694 |
10 CM 1 | 09/01/2017 | 0 | 5,690 |
11 CM 1 | 20/01/2017 | 0 | 86,687 |
12 CM 1 | 28/03/2017 | 0 | 8,605 |
13 CM 1 | 18/05/2017 | 0 | 8,219 |
14 CM 1 | 15/06/2017 | 0 | 3,908 |
Team | Tra Reference | Group Name | Status | Amount (Latest m1 +m2 from first table) |
Pro Zen | 30732AB | JRB | OPEN | 3,908 |
Thanks,
Ganesh
more details please about your data and what you need to do.
regards,
Your requirement is not clear. Can you share sample application or data in excel format?
Hi All,
I got data as shown in attached sheet1 and I need result as Sheet2, This needs a runtime calculation, an expression will be very helpful or guide me to achieve this in script level.
Thanks,
Ganesh
is this what you need?
!
please mark as helpfull if it is for you.
regards,
Hi Kh fou,
I need last record which is latest, the sum of m1 and m2 i.e. 0+3908 = 3908, Please refer to the data provided in the thread.
Result:
Team | TraReference | Group Name | Status | Amount (Latest m1 +m2 from first table) |
Pro Zen | 30732AB | JRB | OPEN | 3,908 |
Thanks,
Ganesh
can anyone help me on this?
Try this?
Team:
LOAD *, Num(Floor(Date#(Date, 'DD/MM/YYYY'))) AS DateId;
LOAD [T Number],
Date,
m1,
m2,
Team,
[Tra Reference],
[Group Name],
Status
FROM
(ooxml, embedded labels, table is Sheet1);
LET vMaxDateId = Peek('DateId', -1);
Final:
LOAD [T Number],
IF(DateId = '$(vMaxDateId)', 1, 0) AS MaxFlag
Resident Team;
Then use straight table and add dims and expr like below:
Try this
T:
LOAD [T Number],
date(alt(Date#(Date,'DD/MM/YYYY'),Date#(Date,'DD-MM-YYYY')),'DD/MM/YYYY')AS Date,
m1, m2, Team,
[Tra Reference],
[Group Name], Status
FROM doc.xlsx
(ooxml, embedded labels, table is Sheet1);
Expression : =sum({<Date={"$(=max(Date))"}>}m1)+sum({<Date={"$(=max(Date))"}>}m2)