max sales date

[Anonymous]

Good day!

I need to solve some problem.

There is an example in xls file with necessary result

I need to get sales with max date, where total sales for all SKU's in one Distributors > 0 (date must be the same for all SKU's for one distributors)

expression, where i calculate sales with max date:

FirstSortedValue(aggr(sum(Sales),Distributor_Name,SKU,Date),-aggr(max({<Sales={">0"}>}Дата),Distributor_Name,SKU,Date))

expression, where i calculate max date, where sales > 0:

max({<sales={">0"}>}Дата)

but result is wrong...

Thanks for all answers!

[MK_QSL]

Kindly provide sample data....

[Anonymous]

in pivot table Quantity i show quantity and date.

my task is to get quantity with max date, where total for all types >0

in my example  i need:

for point 2 -  date 22.09.2014 for each types, because it's max date, where point 2 has any quantity

for point 3 - 23.12.2014 for each types, because it's max date, where point 3 has any quantity

for point 3 - 21.12.2014 for each types...

...

Thank you!

[MK_QSL]

=Date(Max({<ProductType = {"=SUM(Quantity) > 0"}>}TOTAL <Spoint> Date))

[Anonymous]

with your expression..

for point 2 i get 22.12.2014, but it's wrong, because total sum of quantity with this date is 0. necessary date for point 2 is 22.09.14, where total quantity>0 (12)

such condition must be for all Spoints

Thank you!

[MK_QSL]

=Date(Max({<Quantity = {">0"}>}TOTAL <Spoint> Date))

[Anonymous]

it's working!

can you write me, how must i connect this condition with quantity in first expression ?

(quantity with max date, where total >0)

[MK_QSL]

Dont understand your requirements properly but try any from below... i think you can get what you want...

SUM(Quantity)

or

SUM({<Quantity = {">0"}>}TOTAL <Spoint> Quantity)

[Anonymous]

i want to get quantity for each type's with date, which you write in expression =Date(Max({<Quantity = {">0"}>}TOTAL <Spoint> Date))

necessary result on picture

[MK_QSL]

Didn't understand what you want and what is the logic behind your result... Please explain