topic Re: Current and Previous values in UI in QlikView

Current and Previous values in UI

Anonymous — Mon, 18 Dec 2017 08:42:33 GMT

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:

Re: Current and Previous values in UI

sunny_talwar — Mon, 18 Dec 2017 13:25:47 GMT

This is def. doable using set analysis, but how are you providing the limit? Is this via an input box?

Re: Current and Previous values in UI

Anonymous — Tue, 19 Dec 2017 18:13:43 GMT

Dear Sunny

Yes input will be provided via input box. Also the last modified date must be looked against Row ID and Employee ID (i.e Unique Row ID and Employee ID)

Thanks for your help.

Re: Current and Previous values in UI

sunny_talwar — Tue, 19 Dec 2017 19:24:54 GMT

Like the attached

Re: Current and Previous values in UI

Anonymous — Wed, 20 Dec 2017 08:54:56 GMT

Hi Sunny,

Yes Exactly. Can you share me the file please.

Re: Current and Previous values in UI

sunny_talwar — Wed, 20 Dec 2017 16:31:55 GMT

It is already attached above

Re: Current and Previous values in UI

Anonymous — Wed, 20 Dec 2017 17:05:24 GMT

Hi Sunny, I can see Image alone. Can you please share the qvw file ?

Re: Current and Previous values in UI

sunny_talwar — Wed, 20 Dec 2017 17:20:16 GMT

Open this link

Re: Current and Previous values in UI

Anonymous — Mon, 25 Dec 2017 18:34:34 GMT

Thanks Sunny.

Have checked the file. It has current selections populated as per the date selected in list box. May be I am not clear with my requirement here.

Please find below in detail.

Key=Row ID+Employee ID
Only Columns with Previous (i.e. Previous OTD and Previous last_modified_date_time) must be picked from previous values, other column values must be current values.
The output tables must be shown for selected month only. Here Nov 2017 is selected. So record with 31/10/2017 23:45 is not populated in output table.

Source:

Name	Row ID	Employee ID	last_modified_date_time	SLA	OTD	FPR	Priority
A	74	22	31/10/2017 23:45			Yes	1
B	44	22	15/11/2017 16:38	green		Yes	2
C	44	22	14/11/2017 17:32	green	3	Yes	1
H	11	44	17/11/2017 17:32	red	4	No	1
E	33	23	15/11/2017 17:39	green	3	Yes	2
E	44	22	15/11/2017 17:38	green	1	No	3
E	44	22	15/11/2017 17:36	red	3	No	2
F	33	23	17/11/2017 17:39	red	3	No	1
G	44	22	22/11/2017 12:34	red	3	No	2
H	11	44	25/11/2017 12:34	green	4	No	1

Output Table (vInput=15):

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	green	red	1	3	22/11/2017 12:34	15/11/2017 17:38	No	3
33	F	23	green	red	3	3	17/11/2017 17:39	15/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

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	green	red		3	22/11/2017 12:34		No	3
33	F	23	green	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

Re: Current and Previous values in UI

Anonymous — Wed, 27 Dec 2017 06:49:18 GMT

Dear Sunny, Any help or suggestions on this please.

Re: Current and Previous values in UI

sunny_talwar — Wed, 27 Dec 2017 12:32:04 GMT

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

Re: Current and Previous values in UI

Anonymous — Wed, 27 Dec 2017 16:37:49 GMT

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

Re: Current and Previous values in UI

Anonymous — Fri, 29 Dec 2017 09:23:39 GMT

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

Re: Current and Previous values in UI

sunny_talwar — Fri, 29 Dec 2017 17:50:28 GMT

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

Re: Current and Previous values in UI

Anonymous — Mon, 01 Jan 2018 08:29:57 GMT

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.

Re: Current and Previous values in UI

sunny_talwar — Mon, 01 Jan 2018 16:13:02 GMT

Try the attached

Re: Current and Previous values in UI

Anonymous — Wed, 03 Jan 2018 07:52:51 GMT

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.

Re: Current and Previous values in UI

sunny_talwar — Wed, 03 Jan 2018 09:30:19 GMT

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