Comparison between the last state of a rental with the previous one

[foxwhite]

Hi everyone,

I'am dealing with a difficult in manipulating dates with Qlik sense.

I am trying to compare the last date of a rental with the previous one, and checking the state of the rental. Maybe it's not clear so i will give you an example to understand what i want to do. 

So I have this table to follow the rental of cars.

ID_cars	ID_transaction	Date_status	status	Country
12	2053	12/10/2005	available	Brazil
12	2054	12/11/2005	rented	Brazil
12	2055	05/12/2005	available	Brazil
12	2056	06/03/2007	rented	Brazil
12	2057	18/10/2008	available	Brazil
13	3050	01/02/2004	rented	UK
13	3051	02/03/2005	available	UK
13	3052	10/11/2006	rented	UK
13	3053	12/02/2007	available	UK
13	3054	13/10/2007	rented	UK
14	3055	14/05/2003	rented	Germany
14	3056	16/02/2005	available	Germany
14	3057	11/06/2008	rented	Germany
14	3058	10/10/2009	available	Germany
14	3059	14/10/2011	rented	Germany

For each Id_cars I have the Id_transaction associated with the status (rented or available) and the date of the status. 

My goal is to calculate the last date of status and the previous one, and check that the status are not the same.

For example : 

ID_cars	ID_transaction	last_date_status	last_status	Previous_date_status	Previous_status	Check status
12	2057	18/10/2008	available			ok
12	2056			06/03/2007	rented	ok

The second table, it's may be not the good representation but it was to give you an idea if what I want to do.

Thank you for your helping !

[Kashyap_R]

Hi

Please find the answer in attachment

Hope this helps

Thanks

[Kushal_Chawda]

You can create straight table

Dimension:

ID_Cars

ID_Transaction

Expressions:

1) Latest Status
=aggr(FirstSortedValue(status,  -Date_status),ID_cars)

2) Latest Date
=aggr(FirstSortedValue(Date_status,  -Date_status),ID_cars)

3) Previous Date Status
=aggr(FirstSortedValue(status,  -Date_status,2),ID_cars)

4) Previous Status
=aggr(FirstSortedValue(Date_status,  -Date_status,2),ID_cars)

Note: In Data handling properties uncheck 'suppress zero values'

[foxwhite]

Hi Kashyap,

I've tried your app, and it seem to be worked. 

In additonnaly i want to do a check for all transactions. I can't have successively the same status for an Id_car. The status can't not be "rented" and the previous one "rented"also.

For example :

ID_cars	Status	Date_status	Check
12	available	12/10/2005
12	rented	12/11/2005	ok
12	available	05/12/2005	ok
12	rented	06/03/2007	ok
12	available	18/10/2008	ok

But it was this :

ID_cars	Status	Date_status	Check
12	rented	12/10/2005
12	available	12/11/2005	ok
12	rented	05/12/2005	ok
12	rented	06/03/2007	wrong
12	available	18/10/2008	ok

The car is rented successively the 5/12/2005 and 06/03/2007 without beeing available between.  I can use the expression given in your app, but the main difficult is that the number of transactions per ID_cars is variable. For this example i have 5 transactions (so 5 status to check), but for another cars I can have more or less than this number.

If you can help me or someone else, that would be great.

Many thanks for your first solution.

[Kashyap_R]

Hi

Can u please share me the QVF file, I will try and get back to u

Thanks

[foxwhite]

Here the QVF File.

Thank you.