12 Replies Latest reply: Aug 6, 2014 5:44 AM by Андрей Шепель

# max sales date

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...

• ###### Re: max sales date

Kindly provide sample data....

• ###### Re: Re: max sales date

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!

• ###### Re: max sales date

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

• ###### Re: Re: max sales date

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!

• ###### Re: max sales date

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

• ###### Re: max sales date

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)

• ###### Re: max sales date

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)

• ###### Re: Re: max sales date

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

• ###### Re: max sales date

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