topic Re: Get sum between 2 special record that user select in QlikView

Get sum between 2 special record that user select

marjan_it — Sat, 16 Nov 2024 19:23:44 GMT

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

Re: Get sum between 2 special record that user select

Taoufiq_Zarra — Wed, 25 Dec 2019 11:13:07 GMT

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

Re: Get sum between 2 special record that user select

Kushal_Chawda — Wed, 25 Dec 2019 11:38:53 GMT

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)

Re: Get sum between 2 special record that user select

marjan_it — Sat, 28 Dec 2019 08:47:57 GMT

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

Re: Get sum between 2 special record that user select

Kushal_Chawda — Sat, 28 Dec 2019 13:17:12 GMT

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

Re: Get sum between 2 special record that user select

Taoufiq_Zarra — Sat, 28 Dec 2019 12:18:31 GMT

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

Re: Get sum between 2 special record that user select

Kushal_Chawda — Sat, 28 Dec 2019 13:08:57 GMT

@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.

Re: Get sum between 2 special record that user select

marjan_it — Sun, 29 Dec 2019 05:37:33 GMT

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?!!

Re: Get sum between 2 special record that user select

Kushal_Chawda — Sun, 29 Dec 2019 11:05:02 GMT

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

Re: Get sum between 2 special record that user select

marjan_it — Mon, 13 Jan 2020 08:40:25 GMT

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?

Re: Get sum between 2 special record that user select

Kushal_Chawda — Mon, 13 Jan 2020 10:49:26 GMT

Not sure I understand that. Can you provide example?

Re: Get sum between 2 special record that user select

marjan_it — Mon, 13 Jan 2020 11:50:54 GMT

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
];

Re: Get sum between 2 special record that user select

Kushal_Chawda — Mon, 13 Jan 2020 14:44:36 GMT

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

Re: Get sum between 2 special record that user select

marjan_it — Tue, 14 Jan 2020 06:32:28 GMT

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

Re: Get sum between 2 special record that user select

Kushal_Chawda — Tue, 14 Jan 2020 12:07:14 GMT

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

Re: Get sum between 2 special record that user select

marjan_it — Wed, 15 Jan 2020 13:18:20 GMT

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.

Re: Get sum between 2 special record that user select

Kushal_Chawda — Wed, 15 Jan 2020 14:45:17 GMT

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))

Re: Get sum between 2 special record that user select

marjan_it — Sat, 18 Jan 2020 06:17:13 GMT

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.

Re: Get sum between 2 special record that user select

Kushal_Chawda — Sat, 18 Jan 2020 10:48:45 GMT

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

Re: Get sum between 2 special record that user select

marjan_it — Sat, 18 Jan 2020 13:20:47 GMT

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.