finding max stage and delay in process

[swiftfahad]

hi grus....!!!!

I have the following table

Demand_no	Process	Order	Process_date	Process_id
IT-922	Demand	0	01-01-2016	sdfdsafs
IT-922	IT	1	05-01-2016	sdfsafsd
IT-922	Tender	2	08-01-2016	sdfsadfsdf
IT-922	Quotation	3	12-01-2016	sdfsdfsxcv
IT-922	Po	4	05-02-2016	sdfrjgh
IT-922	Payment	5	10-02-2016	dtyiyum
HR-899	Demand	0	14-01-2016	ghiyhjgh
HR-899	IT	1	18-01-2016	yuiyukj
HR-899	Tender	2	20-01-2016	hnmvbxc
HR-899	Po	4	25-01-2016	gfhgftyty
DD-2342	Demand	0	13-03-2016	sdfer
DD-2342	IT	1	15-03-2016	eertet
DD-2342	Tender	2	20-03-2016	weregdfgf

now i have shown 3 different demand each of them has different max process..

IT-922 is in payment process

HR-899 is in Po process

DD-2342 is in Tender process

Requirement 1:now i want to show these above maximum process in a table

Demand_no	Current_Stage
IT-922	Payment
HR-899	Po
DD-2342	Tender

Requirement 2: I also want a separate table showing delay from each process to another

Demand_no	Process	Delay in days
IT-922	Demand	0	ff
IT-922	IT	4
IT-922	Tender	3
IT-922	Quotation	4
IT-922	Po	23
IT-922	Payment	5
HR-899	Demand	0
HR-899	IT	4
HR-899	Tender	2
HR-899	Po	5
DD-2342	Demand	0
DD-2342	IT	2
DD-2342	Tender	5

plzzz help

[Kushal_Chawda]

Create Table 1

Dimension: Demand_no

Expression:

= FirstSortedValue(Process, -ProcessDate)

Create Table2

Dimension:

Demand_no

Process

Expression:

=if(Demand_no<>above(Demand_no),0, Date - above(Date))

Upadate: After seeing Jonathan's reply

[jonathandienst]

Agree with kush141087‌ for the first table

I would use this expression for table 2:

=If(Demand_no = Above(Demand_no), Date - Above(Date), 0)

Assumes that the Date field is a numeric QV date value.

[Kushal_Chawda]

Yes, good catch. I missed that.

[swiftfahad]

Thanks dear jonathan and kushal.. First table is working fine but in second table process are not showing in order. Which causing wrong delay calculation.

proper order is demand,IT,tender,quotation,po,payment

[swiftfahad]

thanks kushal for the replay table 1 is perfect... now can I draw count chart on this table 1?

for example

remember following graph is genrated from table 1 you have advice.I want to show process wise number of demands currently stuck in

process	number of demand
demand	2
IT	4
tender	4
quotation	3
po	5
payment	3