topic Re: Re: sales with latest date in QlikView

sales with latest date

Anonymous — Wed, 06 Aug 2014 11:07:25 GMT

Good day.

My problem:

There is pivot table "Quantity with latest date" in attached model

In expression i calculate max date for each sales point, where total quantity >0

In second expression i want to show quantity with date, got in first expression.

Thank you!

Re: sales with latest date

Gysbert_Wassenaar — Thu, 07 Aug 2014 07:14:28 GMT

Try FirstSortedValue({<Quantity = {">0"}>}TOTAL <Spoint> Quantity,-Date)

Re: Re: sales with latest date

Anonymous — Thu, 07 Aug 2014 07:21:59 GMT

It's working!

but quantity is not aggregated by types. I see the same value for all types.

Re: Re: Re: sales with latest date

Yousef_Amarneh — Thu, 07 Aug 2014 07:41:45 GMT

try this please

Re: Re: sales with latest date

Anonymous — Thu, 07 Aug 2014 07:48:27 GMT

thank you!

max date is calculated correctly.

But for Quantity:

for example:

for Point 3 max sales date is 23.12.2014 - but quantity isn't correct: 32 is for 23.12.2014, 33 is for 23.09.2014

for Spoint 3 for all types date must be 23.12.2014 (max sales date)

and such condition for other points

Thank you!

Re: Re: sales with latest date

Anonymous — Thu, 07 Aug 2014 07:49:44 GMT

34 instead 32

sorry

Re: Re: sales with latest date

Gysbert_Wassenaar — Thu, 07 Aug 2014 08:00:51 GMT

Try =sum(aggr(FirstSortedValue({<Quantity = {">0"}>}Quantity,-Date),ProductType,Spoint))

Re: sales with latest date

Anonymous — Thu, 07 Aug 2014 08:07:37 GMT

for this expression quantity isn't correct...

for example: Point 3: there must be only value for type 1 :33, for type 3 there mus not be any value, because date for it is 23.09.2014 (not max date)

Re: Re: sales with latest date

Yousef_Amarneh — Thu, 07 Aug 2014 08:23:09 GMT

please see this.

Re: sales with latest date

Anonymous — Thu, 07 Aug 2014 08:36:16 GMT

Let see point 3:

23.12.2014 : 34

23.09.2014 : 33

I need quantity only for max date (for point 3 - 23.12.2014 )

if for some type there are not any quantity with max date, there must be null value

Thank you

Re: sales with latest date

Yousef_Amarneh — Thu, 07 Aug 2014 08:39:32 GMT

but there are 2 product types for Point3, do you want only the max date of spoint regardless the product type?

Re: Re: sales with latest date

Anonymous — Thu, 07 Aug 2014 08:49:37 GMT

yes.

i want to show quantity by max date.

max date must be the same for each product type.

thank you!

Re: Re: Re: sales with latest date

Yousef_Amarneh — Thu, 07 Aug 2014 10:59:40 GMT

I found a solution but in the load script, please see the attached.

Re: Re: Re: sales with latest date

lironbaram — Tue, 12 Aug 2014 18:51:49 GMT

hi have a look at the attach example

hope it what you looked for