topic second last car purchase date in QlikView

second last car purchase date

harsha087 — Thu, 19 Aug 2021 17:58:10 GMT

Hi All,

Below is the data ,

Purchase_Date	Car_Brand	Price	YearMonth	Type
09/12/2020	Volvo	3822677	202012	SEDAN
02/11/2020	Volvo	1599298	202011	SEDAN
15/11/2020	Volvo	1954432	202011	SEDAN
16/11/2020	Mercedes	9478128	202011	ELECTRIC
16/11/2020	Mercedes	6471128	202011	SEDAN
19/11/2020	Mercedes	6049416	202011	SEDAN
22/11/2020	Mercedes	8705679	202011	SEDAN
19/10/2020	Jaguar	4713860	202010	ELECTRIC
20/10/2020	Jaguar	9486007	202010	ELECTRIC
21/10/2020	Jaguar	4350439	202010	ELECTRIC
28/10/2020	Jaguar	3185625	202010	ELECTRIC

i need to show second last car purchase date where brand is 'Mercedes’ and car type is 'SEDAN' without using hardcoded date or year-month in set analysis in KPI OR Text box.

however, i have created rank and written below expression, but i m not getting values

max({1<Car_Brand={"Mercedes"},Type={"SEDAN"},rank={"2"}>}Purchase_Date)

let me know if i m wrong ..

Re: second last car purchase date

Vegar — Thu, 19 Aug 2021 20:32:45 GMT

You can fetch the date by using max([Purchase date], 2) to get the second largest value. See image below.

Re: second last car purchase date

harsha087 — Fri, 20 Aug 2021 04:25:42 GMT

Thanks for ur quick response ..

When i write the same logic on my data .. below is what i can see .. Could you help when i did a mistake ..

I don't see values for Mercedes .. Attached is the whole data i have it ..

Re: second last car purchase date

Vegar — Fri, 20 Aug 2021 07:30:02 GMT

I don't get the same output from you Excel file.

See attached "Car Sales.qvf"

Re: second last car purchase date

harsha087 — Fri, 20 Aug 2021 08:48:59 GMT

Thank You ... 🙂

Re: second last car purchase date

Vegar — Fri, 20 Aug 2021 08:49:58 GMT

Glad I could be of help!