Get sum between 2 special record that user select

[marjan_it]

Hi guys,

I have a table like below. I want to have two list box with status. start status and end status. when user select start status and end status we can show sum (duration) between these tow status for each message code. 

for example if we select  first status =2 and end status=5 we have two number. for messagecode=1 we have 100 and for messagecode=2 we have 70. what should I do?

MessageCode	Messagelog	status	duration
1	101	1	10
1	102	2	20
1	103	3	40
1	104	4	10
1	105	5	30
1	106	6	20
1	107	7	10
2	108	1	50
2	109	2	30
2	110	3	10
2	111	4	10
2	112	5	20
2	113	6	30
2	114	7	10

[Taoufiq_Zarra]

Hi,

a qlikview version is attached

The principle is to save the two fields Start and End separately in two variables.
and a set analysis to do the calculation.

results

[Kushal_Chawda]

Create two inline table from data like below

Data:
LOAD
MessageCode,
Messagelog,
status,
duration
FROM [lib://Qlik web]
(html, utf8, embedded labels, table is @1);

FirstStatus:
Load Distinct status as FirstStatus
Resident Data;

EndStatus:
Load Distinct status as EndStatus
Resident Data;

Now you can use below expression in your chart

=sum({<status={">=$(=min(FirstStatus))<=$(=max(EndStatus))"}>}duration)

[marjan_it]

thanks for your complete answer thats  correct for this sample but I made a mistake for asking my question. this data is sample and  my data extract from sql and its a little different.

My status is not sorted by Id. Maybe I select from status 6 to status 1. it should be status between time status 6 is started till the status that the time of status 1 is finished.

for example n the below title the status between 2 and 5 will be changed. Duration between Status 2 and 5 for Messagecode=1 would be 90. 

please help me again .

MessageCode	Messagelog	status	duration	Time
1	102	2	20	3/21/17 1:19 PM
1	101	1	10	3/27/17 9:48 AM
1	104	4	10	3/27/17 10:16 AM
1	106	6	20	3/29/17 11:05 AM
1	105	5	30	3/29/17 11:21 AM
1	107	7	10	4/5/17 9:57 AM
1	103	3	40	4/8/17 1:50 PM
2	109	2	30	3/21/17 1:19 PM
2	108	1	50	3/27/17 9:48 AM
2	111	4	10	3/27/17 10:16 AM
2	110	3	10	3/29/17 11:05 AM
2	112	5	20	3/29/17 11:21 AM
2	114	7	10	4/5/17 9:57 AM
2	113	6	30	4/8/17 1:50 PM

[Kushal_Chawda]

First set your timestamp variable in script as your actual timestamp format in your data

SET TimestampFormat='MM/DD/YYYY h:mm TT';

T1:
LOAD
MessageCode,
Messagelog,
status,
duration,
"Time"
FROM [lib://Qlik web]
(html, utf8, embedded labels, table is @2);

FirstStatus:
Load Distinct status as FirstStatus
Resident T1;

EndStatus:
Load Distinct status as EndStatus
Resident T1;

Create two variables on front end

vFirstStatus

= min(FirstStatus)

vEndStatus

=max(EndStatus)

Now you can use the below expression in chart with messagecode as dimension

=sum({<Time={">=$(=min({<status={'$(vFirstStatus)'}>}Time))<=$(=max({<status={'$(vEndStatus)'}>}Time))"}>}duration)

If no FirstStatus and EndStatus selected,then default to sum of all duration

=if(GetSelectedCount(FirstStatus)=0 and GetSelectedCount(EndStatus)=0,sum(duration),
sum({<Time={">=$(=min({<status={'$(vFirstStatus)'}>}Time))<=$(=max({<status={'$(vEndStatus)'}>}Time))"}>}duration))

Note:

If your status field is text, then use below in variable

=getfieldselections(FirstStatus)

=getfieldselections(EndStatus)

Also make sure that for both FirstStatus & EndStatus , 'always one selected value' option is checked

[Taoufiq_Zarra]

another approach:

create an ID for each line.

DATA:
LOAD rowNo()  as ID,* INLINE [
MessageCode,	Messagelog,	status	,duration	,Time
1,	102,	2,	20,	3/21/17 1:19 PM
1	,101,	1,	10,	3/27/17 9:48 AM
1,	104,	4,	10,	3/27/17 10:16 AM
1	,106,	6,	20,	3/29/17 11:05 AM
1	,105,	5,	30,	3/29/17 11:21 AM
1	,107,	7,	10,	4/5/17 9:57 AM
1	,103,	3,	40,	4/8/17 1:50 PM
2	,109,	2,	30,	3/21/17 1:19 PM
2	,108,	1,	50,	3/27/17 9:48 AM
2	,111,	4,	10,	3/27/17 10:16 AM
2	,110,	3,	10,	3/29/17 11:05 AM
2	,112,	5,	20,	3/29/17 11:21 AM
2	,114,	7,	10,	4/5/17 9:57 AM
2	,113,	6,	30,	4/8/17 1:50 PM
];

start:
load distinct status as Start_Status resident DATA;

end:
load distinct status as End_Status resident DATA;

save the line of each selected status

Variables:

=GetFieldSelections(Start_Status)
=GetFieldSelections(End_Status)
=Max({<status= {"$(StartStatus)"}>} ID)
=Max({<status= {"$(EndStatus)"}>} ID)

then calculate the duration between the selected statuses.

sum({<ID={">=$(StartID)<=$(EndID)"}>} duration)

attached the Qlikview file

[Kushal_Chawda]

@Taoufiq_Zarra 

There is two problems with this approach

1) If data is not sorted on TIME & MESSAGECODE this solution won't work. I think this is sample data that is why it is sorted on TIME. So first you need to sort the data on messagecode & time

2) This works only when you select a particular messagecode and which is not the case. When you select FirstStatus & EndStatus, bydefault chart should display correct sum of duration for both messagecode.

[marjan_it]

Yeah your right. It does work just for one Messagecode. I have to say flow statuslogId is sorted for each Messagecode. Not for all of Messagecode. But for each Messagecode its sorted. can this help for solving this problem?!!

[Kushal_Chawda]

have you tried what I suggested? using that you don't need to sort anything. it works as expected

[marjan_it]

Yes I tried it. It works but has a problem. if  user select start_status=1 and End_status=8 , the chart should not consider the MessageCode that have not record by status 8. do you understand?