sum(TOTAL cost) of the latest date

[thanhphongle]

Hello Community

this is my table

Date	Unit Cost	Quantity
01.01.2016	10	5
01.01.2016	10	3
01.04.2016	50	4
01.10.2016	100	3
01.10.2016	10	5
01.10.2016	200	2
01.10.2016	150	4

How can I get the total cost sum([Unit Cost]*Quantity) for the last date 01.10.2016 ?

QV should return: 100*3 + 10*5 + 200*2 + 150*4 = 1350

[sunny_talwar]

You issue is that you have use Sum(....Sum(... function.... you need to add Aggr() between the two sum function

Sum(Aggr(Sum(.....

But since I am not entirely sure what your goal is, it is difficult to say. What is the numerical output you expect to see within your sample?

[thanhphongle]

if you add contract_begin as a listbox you ll see that 01.10.2017 is the latest date.

the main expression is: (1-(1/(sum(cost_acquisition*cost_rate_reg_perc*(contract_term_reg+lm_term)))*sum(cost_acquisition*cost_rate_reg_csc_perc*(contract_term_reg+lm_term))))

the function should return me the calculation of the main expression referring to the latest date 01.10.2017

[sunny_talwar · Accepted solution]

It repeats the same number for each of your date

But this is sort of working

=(1-(1/(sum({<contract_begin = {"$(=Date(Max(contract_begin), 'DD.MM.YYYY'))"}>} cost_acquisition*cost_rate_reg_perc*(contract_term_reg)))*sum({<contract_begin = {"$(=Date(Max(contract_begin), 'DD.MM.YYYY'))"}>} cost_acquisition*cost_rate_reg_csc_perc*(contract_term_reg))))

Your date format is not MM.DD.YYYY, it is DD.MM.YYYY

[thanhphongle]

Oh my god, you r defenitly the best !! It works. Thank you very much !!!