Current and Previous values in UI

[Anonymous]

     Dear Guys,

Need help with the below requirement in UI.

a) Current values are the maximum values in table.

b) For previous values I need a option in UI to compare with current date and limit provided. (For eg: if I select 15, previous value should be compared between max date to 15th of that particular month)

Input:

Name	Row ID	Employee ID	last_modified_date_time	SLA	OTD
A	44	22	31/10/2017 23:45
B	44	22	15/11/2017 16:38	green
C	44	22	14/11/2017 17:32	green	3
D	44	22	15/11/2017 17:34	green	3
E	44	22	15/11/2017 17:36	green	3
F	44	22	17/11/2017 17:39	red	3
G	44	22	22/11/2017 12:34	red	3

Output:

Row ID	Name	prev_perfro	curr perf	prev_control_status	current_control status	changed date	prev chagened date
44	G	green	red	3	3	22/11/2017 12:34	15/11/2017 17:3

Note:

[sunny_talwar]

Can you explain the output when vInput = 17? I am not sure how is row with RowID 44 getting populated?

[Anonymous]

Thanks for pointing out. There is a typo error in output table when vInput=17. Previous SLA will be blank for Rowi 44 and 33 (i.e. same as Previous OTD  and Previous last_modified_date_time).

Eg: Row ID=14 and Employee ID=22 when vInput=17

We need to check if there is any change between 17/11/2017 (including time stamp) to current available date (i.e. 22/11/2017 12:34). As per the data there is no records with change in SLA, so it's should be blank or shown as No Change).

Output Table (vInput=17):

Row ID	Name	Employee ID	Previous SLA	Current SLA	Previous OTD	Current OTD	Current last_modified_date_time	Previous last_modified_date_time	FPR	Priority
44	G	22		red		3	22/11/2017 12:34		No	3
33	F	23		red		3	17/11/2017 17:39		No	1
11	H	44	red	green	4	4	25/11/2017 12:34	17/11/2017 17:32	No	1

[Anonymous]

Can you please have a look and let me know if any information is required.

[sunny_talwar]

Try attached

Dimension

Row ID

Expressions

Name

FirstSortedValue(Name, -last_modified_date_time)

Employee ID

=[Employee ID]

Previous SLA

FirstSortedValue({<Date = {"$(='>=' & Date(MakeDate(Year(Max(Date)), Month(Max(Date)), vInput)))"}>}SLA, -last_modified_date_time, 2)

Current SLA

FirstSortedValue(SLA, -last_modified_date_time)

Previous OTD

FirstSortedValue({<Date = {"$(='>=' & Date(MakeDate(Year(Max(Date)), Month(Max(Date)), vInput)))"}>}OTD, -last_modified_date_time, 2)

Current OTD

FirstSortedValue(OTD, -last_modified_date_time)

Current last_modified_date_time

=TimeStamp(Max(last_modified_date_time))

Previous last_modified_date_time

=TimeStamp(Max({<Date = {"$(='>=' & Date(MakeDate(Year(Max(Date)), Month(Max(Date)), vInput)))"}>}last_modified_date_time, 2))

When vInput is 15

When vInput is 17

[Anonymous]

Hi Sunny,

The chart is showing all the the values with respect to previous max available times stamp, but not based on previous modified SLA.

I have just changed one record in input data to have a look.

Existing:

E, 44, 22, 15/11/2017 17:38, green, 1, No, 3

New (Just changed green to red)

E, 44, 22, 15/11/2017 17:38, red, 1, No, 3

Output (vInput is 15):

Row ID	Employee ID	Name	Previous SLA	Current SLA	Previous OTD	Current OTD	Current last_modified_date_time	Previous last_modified_date_time
44	22	G	red	red	1	3	22/11/2017 12:34	15/11/2017 17:38

Here Previous SLA and Current SLA are same (i.e. red)

Expected Output (vInput=15):

Row ID	Employee ID	Name	Previous SLA	Current SLA	Previous OTD	Current OTD	Current last_modified_date_time	Previous last_modified_date_time
44	22	G	green	red		3	22/11/2017 12:34	15/11/2017 16:38

Can you please have a look.

[sunny_talwar]

Try the attached

[Anonymous]

Thanks Sunny. Previous SLA and Previous OTD are getting changed as expected. But Previous last_modified_date_time is getting displayed for all the cases.

Output (vInput=15): This is showing correct previous last_modified_date_time

Row ID	Employee ID	Name	Previous SLA	Current SLA	Previous OTD	Current OTD	Current last_modified_date_time	Previous last_modified_date_time
44	22	G	green	red		3	22/11/2017 12:34	15/11/2017 16:38
33	23	F	green	red	3	3	17/11/2017 17:39	15/11/2017 17:39
11	44	H	red	green	4	4	25/11/2017 12:34	17/11/2017 17:32

Output (vInput=17): This must display blank previous last_modified_date_time as there is not change in Previous SLA.

Row ID	Employee ID	Name	Previous SLA	Current SLA	Previous OTD	Current OTD	Current last_modified_date_time	Previous last_modified_date_time
44	22	G		red		3	22/11/2017 12:34	15/11/2017 16:38
33	23	F		red		3	17/11/2017 17:39	15/11/2017 17:39
11	44	H	red	green	4	4	25/11/2017 12:34	17/11/2017 17:32

Can you please have a look.

[sunny_talwar]

Hahahahaha, is there any other changes you would like me to make sir?