topic Re: Re: max sales date in QlikView

max sales date

Anonymous — Tue, 05 Aug 2014 05:42:42 GMT

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!

Re: max sales date

MK_QSL — Tue, 05 Aug 2014 07:58:46 GMT

Kindly provide sample data....

Re: Re: max sales date

Anonymous — Tue, 05 Aug 2014 08:30:34 GMT

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

MK_QSL — Tue, 05 Aug 2014 08:56:49 GMT

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

Re: Re: max sales date

Anonymous — Tue, 05 Aug 2014 09:30:23 GMT

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!

Re: max sales date

MK_QSL — Tue, 05 Aug 2014 09:43:46 GMT

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

Re: max sales date

Anonymous — Tue, 05 Aug 2014 10:09:14 GMT

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

MK_QSL — Tue, 05 Aug 2014 10:21:31 GMT

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

SUM(Quantity)

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

Re: Re: max sales date

Anonymous — Tue, 05 Aug 2014 10:39:23 GMT

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

MK_QSL — Tue, 05 Aug 2014 10:46:16 GMT

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

Re: Re: max sales date

Anonymous — Tue, 05 Aug 2014 10:53:52 GMT

explanation in attached file

hoped, that you will understand what i need 🙂

thank you!!

Re: Re: max sales date

Anonymous — Wed, 06 Aug 2014 09:37:50 GMT

explanation in attached file

hoped, that you will understand what i need 🙂

thank you!!

Re: Re: max sales date

Anonymous — Wed, 06 Aug 2014 09:44:55 GMT

Necessary result