Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try like:
=FirstSortedValue(Aggr(SUM( QTY * UNIT_PRICE) + SUM(TAX_AMOUNT),Org, ItemCode, [PO Date]), -[PO Date])
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.
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
In your sample, I found it to be a format issue. Check the sample attached to find how to go about it.
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 | - |
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.
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 |
please find the attached ...
I am not getting your reply..
i cant view
On Mon, Mar 7, 2016 at 11:08 AM, Amit Biswas <qcwebmaster@qlikview.com>
all Dates is in DD--MM--YYYY HH:MM format