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

[Kushal_Chawda]

Not sure I understand that. Can you provide example?

[marjan_it]

For example in this example we add Messagecode=3.

if we select start_status=1 and End_status=7 it shouldnt consider MessageCode=3 in a chart. it shouldnt be accumulate sum duration between thee two status for messagecode=3 because it dose not have status=7

LOAD * INLINE [
MessageCode, Messagelog, status, duration, Time
1, 101, 1, 10, 3/21/2017 13:19
1, 102, 2, 20, 3/27/2017 9:48
1, 103, 3, 40, 3/27/2017 10:16
1, 104, 4, 10, 3/29/2017 11:05
1, 105, 5, 30, 3/29/2017 11:21
1, 106, 6, 20, 4/5/2017 9:57
1, 107, 7, 10, 4/8/2017 13:50
2, 108, 1, 50, 3/21/2017 13:19
2, 109, 2, 30, 3/27/2017 9:48
2, 110, 3, 10, 3/27/2017 10:16
2, 111, 4, 10, 3/29/2017 11:05
2, 112, 5, 20, 3/29/2017 11:21
2, 113, 6, 30, 4/5/2017 9:57
2, 114, 7, 10, 4/8/2017 13:50
3, 115, 1, 5, 3/21/2017 13:19
3, 116, 3, 10, 3/21/2017 13:19
3, 117, 8, 15, 4/8/2017 13:50
];

[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 below variables on front end

vFirstStatus

= min(FirstStatus)

vEndStatus

=max(EndStatus)

vStartTime
=timestamp(rangemin(min({<status={'$(vFirstStatus)'}>}Time),max({<status={'$(vEndStatus)'}>}Time)))

vEndTime
=timestamp(rangemax(min({<status={'$(vFirstStatus)'}>}Time),max({<status={'$(vEndStatus)'}>}Time)))

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

=if(GetSelectedCount(FirstStatus)=0 and GetSelectedCount(EndStatus)=0,sum(duration),
sum({<MessageCode = p({<status = {'$(vFirstStatus)'}>}) * p({<status= {'$(vEndStatus)'}>}),Time={">=$(vStartTime)<=$(vEndTime)"}>}duration))

Note: In Chart properies->Presentation->Check suppress zero value

[marjan_it]

It does not work.It does not return anything.🤔☹️

[Kushal_Chawda]

Would you be able to share a sample app where you are trying this?

[marjan_it]

Yeah this is my sample data. till now it work for each Messagecode and each Start_Status and End_Status.

I want to show if select  Start_Status  and End_Status it shows the Message that has these 2 status and and the statuses between these 2 status and after that if we select the year it shows the the records that is only on the selected year. 

[Kushal_Chawda]

In your attached file, you have not updated your variable as defined in my previous post. Change your variable

StartID
=timestamp(rangemin(Min({<FlowStatusID= {"$(StartStatus)"}>} FlowStatusLogTime),Max({<FlowStatusID= {"$(EndStatus)"}>} FlowStatusLogTime)))

EndID
=timestamp(rangemax(Min({<FlowStatusID= {"$(StartStatus)"}>} FlowStatusLogTime),Max({<FlowStatusID= {"$(EndStatus)"}>} FlowStatusLogTime)))


Then use below expression

=if(GetSelectedCount(Start_Status)=0 and GetSelectedCount(End_Status)=0,sum(MinuteDurationTime),
sum({<MessageID = p({<FlowStatusID = {'$(StartStatus)'}>}) * p({<FlowStatusID= {'$(EndStatus)'}>}),FlowStatusLogTime={">=$(StartID)<=$(EndID)"}>}MinuteDurationTime))

[marjan_it]

yeah. I  have not updated variable as defined in your previous post because It works with Previous variable. 

It works with both variable. Actually I have to get average of duration for each department . the problem of these expression is that they dont work with any selection. If I select department or year of flowstatuslogtime  the chart does not show anything. 

[Kushal_Chawda]

It should work as far as your data is linked properly.

[marjan_it]

Data is linked. you can test in my sample. Its because of expression that defined as a special time. I think we should add another condition to the expression  that after selecting start_status and end_status if we select year or department data will be filter again. But I dont know how to use it.