Firstsortedvalue

[MT4T]

Hello I have data like bellow.

I need to aggregate it with firstsortedvalue.

Goal is to get an aggregated latest data:

Last available date in active selection and sum of all Pall capacity by In and Out (field WH).

For year I have defined variable.

But my expression does not work.

FirstSortedValue(DData,aggr(sum{1<Year = {$(vYear)}>},"Pall Capacity",WH))

Results should be:

In 2021 - 2021-12-31 IN 12700

In 2021 - 2021-12-31 OUT 29277

In 2022 - 2022-04-15 IN 12700

In 2022 - 2022-04-15 OUT 30277

Data	WH	Area	Pall capacity
2021-12-31	IN	NY01	4100
2021-12-31	IN	NY03	1600
2021-12-31	IN	NY04	2500
2021-12-31	IN	SC3 E	1350
2021-12-31	IN	SC 2	3150
2021-12-31	OUT	SC3 Sec A	4800
2021-12-31	OUT	SC3 Sec C	4450
2021-12-31	OUT	SC3 Sec D	4800
2021-12-31	OUT	SC3 Sec E	2400
2021-12-31	OUT	SC2 Sec F	0
2021-12-31	OUT	Racks	6127
2021-12-31	OUT	Y/N	6700
2021-01-08	IN	NY01	4100
2021-01-08	IN	NY03	1600
2021-01-08	IN	NY04	2500
2021-01-08	IN	SC3 E	1350
2021-01-08	IN	SC 2	3150
2021-01-08	OUT	SC3 Sec A	4800
2021-01-08	OUT	SC3 Sec C	4450
2021-01-08	OUT	SC3 Sec D	5000
2021-01-08	OUT	SC3 Sec E	2400
2021-01-08	OUT	SC2 Sec F	700
2021-01-08	OUT	Racks	6127
2021-01-08	OUT	Y/N	6700
2022-04-15	IN	NY01	4100
2022-04-15	IN	NY03	1600
2022-04-15	IN	NY04	2400
2022-04-15	IN	SC3 E	0
2022-04-15	IN	SC 2	6127
2022-04-15	OUT	SC3 Sec A	4800
2022-04-15	OUT	SC3 Sec C	4450
2022-04-15	OUT	SC3 Sec D	4800
2022-04-15	OUT	SC3 Sec E	2400
2022-04-15	OUT	SC2 Sec F	1000
2022-04-15	OUT	Racks	6127
2022-04-15	OUT	Y/N	6700
2022-01-22	IN	NY01	4100
2022-01-22	IN	NY03	1600
2022-01-22	IN	NY04	2600
2022-01-22	IN	SC3 E	500
2022-01-22	IN	SC 2	6127
2022-01-22	OUT	SC3 Sec A	4800
2022-01-22	OUT	SC3 Sec C	4450
2022-01-22	OUT	SC3 Sec D	4800
2022-01-22	OUT	SC3 Sec E	2400
2022-01-22	OUT	SC2 Sec F	0
2022-01-22	OUT	Racks	6127
2022-01-22	OUT	Y/N	6700

[starke_be-terna]

Hi MT4T!
Please consider these things:
1) Syntax: Your formula cannot work in the current state. Your sum() is not defined properly. I think you wanted to define it as such: sum({1} "Pall Capacity") and the field DData does not exist (I think you meant Data).
2) Semantics: I don't understand why you use such a complex formula. Does this not work?:
Straight Table:
Dimension -> WH, Measure -> date(max(Data)), Measure -> sum([Pall Capacity])
If you need something else, please upload a QVF and elaborate for easier development.
3) About firstsortedvalue() in general: A thing many people have problems with (me included) with firstsortedvalue() is, that it only shows a value if the first sorted value is UNIQUE. If you have two or more possible values, then firstsortedvalue() returns NULL.
I hope I could help you a little bit.
Best regards, Benjamin

[MT4T]

Hi,

You are right, this is because I wanted to simplify the example and create a mess.

Please see a sheet "Occupancy over time" it should depend on these two aggregated tables by date and splitted by IN/OUT.

I wanted to do something like this sum of OUT_Pall_Capacity + IN_Pall_Capacity by date

FirstSortedValue(DData,aggr(sum{1<Year = {$(vYear)}>},OUT_Pall_Capacity + IN_Pall_Capacity))

My goal is creating a dashboard like I have in excel. Where I can see the current/latest (in case of selection) occupancy.

Therefore, I planned to use aggr in filrstsortedvalue because creating of aggregated tables in script for every single measure is a mess.

PICK a year by variable and then see whole latest capacity according to a selection (sometimes I would like to see a Quarter or Month) then do similar for available palls for each IN/OUT separate as you can see on screen bellow