Only show one row per itemid

[Simon4]

Dear all,

I have this table:

I want to create this table:

item	Category	Available stock	date new stock	amount new stock	total in produciton
E0020021	Slangen	-72	10-11-2022	50	50
e002035	Pistolen	-6	3-9-2022	20	37
e0020060	compressoren	-32		0	0
fa0500.0560	compressoren	-14	15-9-2022	5	5
S0000865	Slangen	-75	5-10-2022	55	55
S0000866	Slangen	-54	12-10-2022	50	50
S0000882	Pistolen	-27	7-10-2022	20	20

So in short:

I only want to know the first date of each item and the value of stock that belongs to that day. I also want an collum with the total order value.

This I use for date new stock:

=if(isnull(DATEPRODUCTIONFINISHED),PLANNEDPRODUCTIONENDDATE)

this i use for amount new stock:

sum(if(isnull(DATEPRODUCTIONFINISHED), ORIGINALSUBASSEMBLYQUANTITY))

can some one help me create the table above?

thank you kinldy in advance.

kind regards,

Simon

[rubenmarin · Accepted solution]

Hi, if you want a fixed table (meaning that selections in dates won't give another result) it will be better to do the operations in script to add a flag where it has '1' for the last record of each item.

In chart you can try adding a calculated dimension that only shows the max date for each item, and uncheck the option to show null values, the calculated dimension could be something as:

Aggr(If(Date=Max(TOTAL <Item> Date), Date),Item,Date)

You'll need to adapt the expression to your dates and fields.