Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

mayuridg
Contributor

Max Vs Sequential Previous Data

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Max Vs Sequential Previous Data

Try like:

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

19 Replies
MVP
MVP

Re: Max Vs Sequential Previous Data

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.

mayuridg
Contributor

Re: Max Vs Sequential Previous Data

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

MVP
MVP

Re: Max Vs Sequential Previous Data

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

Untitled.png

mayuridg
Contributor

Re: Max Vs Sequential Previous Data

Output should be

    

OrgItemCodeMax DatePrevious DateMax PricePrevious Price
N21XYZ3/2/2016 9:002/3/2016 12:00300500
F24ABC1/3/2016  17:00:00 PM1/3/2016 10:00200100
F24XYZ2/3/2016 12:00-200-
MVP
MVP

Re: Max Vs Sequential Previous Data

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.

mayuridg
Contributor

Re: Max Vs Sequential Previous Data

Org NameItem CodePO DateUnit Price
F24ABC1/3/16 17:00200
F24ABC1/3/16 10:00100
F24XYZ2/3/16 12:00200
N21XYZ1/3/16 17:00100
N21XYZ2/3/16 12:00500
F24ABC29/2/16 10:00100
N21XYZ3/2/16 9:00300
mayuridg
Contributor

Re: Max Vs Sequential Previous Data

please find the attached ...

mayuridg
Contributor

Re: Max Vs Sequential Previous Data

I am not getting your reply..

i cant view

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

mayuridg
Contributor

Re: Max Vs Sequential Previous Data

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