topic Re: Max Vs Sequential Previous Data in QlikView

Max Vs Sequential Previous Data

QlikAngel — Mon, 07 Mar 2016 04:41:06 GMT

Hi,

Please find the attached Sample Data & Output .

I want Org & Itemwise - Max Date of Purchased Price & sequentially Previous Purchase Price for the same Item & Org.

I am getting Max Purchased Price but Previous Purchase price not getting correct.

Please help

Regards,
Mayuri

Re: Max Vs Sequential Previous Data

tresB — Mon, 07 Mar 2016 04:57:54 GMT

I guess it's about date format. Unify the dates in a specific format and try. I.e. - convert all the dates in either 'MM-DD-YYYY' or 'DD-MM-YYYY' format.

Re: Max Vs Sequential Previous Data

QlikAngel — Mon, 07 Mar 2016 05:13:29 GMT

Please check Output its not about date formatting...

Max Purchase date will be in 2016, 2015 or 2014 etc.. i want org wise & Item wise maximum purchase Price & Max of Previous Purchase Price.

example : If i have purchase A item in XYZ Org in 31st Dec 2015 at the Price of 200, same item has been purchased in 02nd Feb 2016 at the price of 500, again the same item purchased on 29th Feb 2016 at the price of 400

Then below will be output

A Item -- XYZ Org --- Max Date will be 29th Feb 2016 --- Price 400 --- Previous Date will be 02nd Feb 2016 --- Price 500

another Example : If i have purchase A item in XYZ Org in 31st Dec 2015 at the Price of 200, same item has been purchased in 02nd Feb 2016 at the price of 500, again the same item purchased on 29th Feb 2016 at the price of 400.

also A Item Purchased in ABC Org in 31st Dec 2015 at 10:00:00Am at the price of 300, same item purchased again in Same Org on 31st Dec 2015 at 17:00:00 Pm at the price of 600, again A item Purchased in Same Org on 01st jan 2016 at 10:00:00 Am at Price 500.

Then my output will be like below:

A Item -- XYZ Org --- Max Date will be 29th Feb 2016 --- Price 400 --- Previous Date will be 02nd Feb 2016 --- Price 500

A item -- ABC Org --- Max Date will be 01st Jan 2016-- Price 500 -- Previous Date will be 31st Dec 2015 at 17:00:00 hrs --- Price 600.

Hope this will be clear the requirement

Re: Max Vs Sequential Previous Data

tresB — Mon, 07 Mar 2016 05:22:38 GMT

In your sample, I found it to be a format issue. Check the sample attached to find how to go about it.

Re: Max Vs Sequential Previous Data

QlikAngel — Mon, 07 Mar 2016 05:29:11 GMT

Output should be

Org	ItemCode	Max Date	Previous Date	Max Price	Previous Price
N21	XYZ	3/2/2016 9:00	2/3/2016 12:00	300	500
F24	ABC	1/3/2016 17:00:00 PM	1/3/2016 10:00	200	100
F24	XYZ	2/3/2016 12:00	-	200	-

Re: Max Vs Sequential Previous Data

tresB — Mon, 07 Mar 2016 05:38:03 GMT

Try to correct your date format in the sample excel and re-upload. I don't see a date like 1/3/2016 for F24 ABC in your sample, it's 03-01-2016.

Re: Max Vs Sequential Previous Data

QlikAngel — Mon, 07 Mar 2016 05:42:15 GMT

Org Name	Item Code	PO Date	Unit Price
F24	ABC	1/3/16 17:00	200
F24	ABC	1/3/16 10:00	100
F24	XYZ	2/3/16 12:00	200
N21	XYZ	1/3/16 17:00	100
N21	XYZ	2/3/16 12:00	500
F24	ABC	29/2/16 10:00	100
N21	XYZ	3/2/16 9:00	300

Re: Max Vs Sequential Previous Data

QlikAngel — Mon, 07 Mar 2016 05:44:47 GMT

please find the attached ...

Re: Max Vs Sequential Previous Data

QlikAngel — Mon, 07 Mar 2016 05:45:37 GMT

I am not getting your reply..

i cant view

On Mon, Mar 7, 2016 at 11:08 AM, Amit Biswas <qcwebmaster@qlikview.com>

Re: Max Vs Sequential Previous Data

QlikAngel — Mon, 07 Mar 2016 05:48:25 GMT

all Dates is in DD--MM--YYYY HH:MM format

Re: Max Vs Sequential Previous Data

tresB — Mon, 07 Mar 2016 05:55:12 GMT

One Max row in your data -

N21

XYZ

2/3/16 12:00

500

but you expect 300 as max price, how?

Re: Max Vs Sequential Previous Data

QlikAngel — Mon, 07 Mar 2016 06:01:49 GMT

OutPut will be below

Org	ItemCode	Max Date	Previous Date	Max Price	Previous Price
N21	XYZ	2/3/2016 12:00	1/3/2016 17:00:00 PM	500	100
F24	ABC	1/3/2016 17:00:00 PM	1/3/2016 10:00	200	100
F24	XYZ	2/3/2016 12:00	-	200	-

Re: Max Vs Sequential Previous Data

QlikAngel — Mon, 07 Mar 2016 06:02:41 GMT

you correct.. i have given correct output

Re: Max Vs Sequential Previous Data

QlikAngel — Mon, 07 Mar 2016 06:06:53 GMT

Amit Biswas i am not able to see your reply..

Re: Max Vs Sequential Previous Data

tresB — Mon, 07 Mar 2016 06:10:58 GMT

Straight table:

Dim1: Org

Dim2: ItemCode

Max Date : = Date(Max([PO Date]))

Prev Date : =Date(Max([PO Date] ,2 ))

Max Price : =FirstSortedValue([Unit Price], -[PO Date])

Prev Price : =FirstSortedValue([Unit Price], -[PO Date] ,2)

Re: Max Vs Sequential Previous Data

QlikAngel — Mon, 07 Mar 2016 08:29:37 GMT

HI,

I HAVE TRIED THIS WITH LIVE DATA..

DATE IS WORKING PERFECTLY

BUT FOR PRICE THERE IS A FORMULA LIKE BELOW

SUM( QTY * UNIT_PRICE) + SUM(TAX_AMOUNT)

HOW IT WILL WORK WITH FIRST SORTED VALUE

AS I HAVE TRIED BUT IT SHOWS ERROR IN EXPRESSION.

SUGGESTIONS ARE WELCOME

Re: Max Vs Sequential Previous Data

tresB — Mon, 07 Mar 2016 08:38:18 GMT

Try like:

=FirstSortedValue(Aggr(SUM( QTY * UNIT_PRICE) + SUM(TAX_AMOUNT),Org, ItemCode, [PO Date]), -[PO Date])

Re: Max Vs Sequential Previous Data

QlikAngel — Mon, 07 Mar 2016 09:29:30 GMT

iTS WORKING BUT FOR SOME OF RECORDS VALUES ARE COMING & FOR SOME ITS NOT..

wHAT WILL BE THE CAUSE?

Re: Max Vs Sequential Previous Data

tresB — Mon, 07 Mar 2016 09:34:52 GMT

May be there are cases where max dates have same max value more than once. If so, try with distinct like:

FirstSortedValue( Distinct Aggr(SUM( QTY * UNIT_PRICE) + SUM(TAX_AMOUNT),Org, ItemCode, [PO Date]), -[PO Date])

Note: you would get syntax error, just ignore that.

Re: Max Vs Sequential Previous Data

QlikAngel — Mon, 07 Mar 2016 09:43:04 GMT

Thank u so much .. its working perfectly