Sum value per every dimension with latest date

[justalkak]

Hi, 

I am trying to create a table with sum of values per every dimension for latest date per dim. The aim is to present the full picture of latest positions.

Example:

Sort, Date, Value

1, 18.02.2024, 100

1, 19.02.2024, 200

1, 20.02.2024, 300

2, 18.02.2024, 400

2, 20.02.2024, 500

3, 10.02.2024, 600

  When selecting Date = 19.02.2024 I will get the Value = 200, but I want to show sum of latest values per every Sort: Value = 200 (for Sort1) + 400 (for Sort2) + 600 (for Sort3).

I have managed to succeed to the point that I can have the latest possible Dates (<= selected Date) with function:

Max({<Date= p({<Date={"<=$(=Max(Date))"} >}Date)>} Date),

however I am failing to have it aggregated by Sort to get the correct sum(Value).

Appreciate your help on this.

[LRuCelver · Accepted solution]

You can get the latest values for every Sort using this expression:

{<Date={"<=$(=Max(Date))"}>} FirstSortedValue(Value, -Date)

(I set the measure's total function to Sum to get the 1200 in the Total row of the table)

To calculate the sum of these as a single value you can use this expression:

{<Date={"<=$(=Max(Date))"}>} Sum(Aggr(FirstSortedValue(Value, -Date), Sort))

Here is the script I used to load the data:

Data:
NoConcatenate Load
    Sort,
    Date(Date#(Date, 'DD.MM.YYYY')) as Date,
    Value
Inline [
    Sort, Date, Value
    1, 18.02.2024, 100
    1, 19.02.2024, 200
    1, 20.02.2024, 300
    2, 18.02.2024, 400
    2, 20.02.2024, 500
    3, 10.02.2024, 600
];

[PhanThanhSon]

@LRuCelver his solution is good, i also found this solution intresting:

Example:

Load * Inline [
Sort, Date, Value
1, 18.02.2024, 100
1, 19.02.2024, 200
1, 20.02.2024, 300
2, 18.02.2024, 400
2, 20.02.2024, 500
3, 10.02.2024, 600
];

MAP_Flag:
MAPPING Load Sort & '-' & Date(min(Date), 'dd.MM.YYYY'),
 1 AS Flag
Resident Example
Group by Sort;    

NoConcatenate
TEMP:
Load *,
 APPLYMAP('MAP_Flag', Sort  & '-' & Date, 0) AS Flag
RESIDENT Example;

DROP TABLE Example;
Rename Table TEMP TO Example;

And a little bit of Set Analysis:

Sum({<Flag = {1}, Date=>} Value)
+
Sum({<Date= {"$(= Replace(GetFieldSelections( Date, ', ', 10000), ', ', chr(39) & ',' & chr(39)))"}>}Value)
-
IF(GetSelectedCount(Date) =0,
0,
Sum({<Flag = {1}, Date=, Sort = {$(=concat(distinct chr(39)&Sort&chr(39), ','))}>}Value))

So my solution is a little bit more complex, but u dont have to use a aggr function.

Best regards Son

[justalkak]

Actually, I have met another problem and now I think it is inevitable to move the calculation to script..;/ but maybe you can enlighten me with it. Is there any possibility to impose on other fields in the front end to be filtered according to what we calculate with the firstsortedvalue? I mean let's say we have other dims for our initial data: 

Sort, Date, Value, ID, Colour

1, 18.02.2024, 100, 1, red,

1, 19.02.2024, 200, 2, blue

1, 20.02.2024, 300, 3, black

2, 18.02.2024, 400, 4, red

2, 20.02.2024, 500, 5, yellow

3, 10.02.2024, 600, 6, pink

Is there any way to filter whole data with all the IDs that go into calculation for 19th Feb (ID 2,4,6) on the point of selecting date = 19.02? Tricky.. and I guess not possible?

Thanks