Converting a Qlik statement to Power BI

[Anonymous]

I am trying to convert a Qlik statement to Power BI and have been unsuccessful.

This is the original statement: 

Avg(Aggr,"(Interval(Min({<CALLS_IN_OUT_SK={2}, CALLS_MEDIACLASS_SK={70634}, CALLS_CALLDISPOSITION_DESC={"All reservations were completed"}>} CALLS_BEGANROUTINGDATETIME_DT) - Min({<CALLS_CALLDISPOSITION_DESC={"Callback Requested"}>} CALLS_BEGANROUTINGDATETIME_DT),'hh:mm:ss')) + (Avg({<CALLS_IN_OUT_SK={1}, CALLS_MEDIACLASS_SK={70634}, CALLS_CALLDISPOSITION_DESC={"Callback Requested"}>} CALLS_NETQTIME)/86400)

This is what I have come up with. Unfortunatly the results are not the same.

CB Response Time =

CALCULATE(MIN(Telephony[CALLS_BEGANROUTINGDATETIME]),Telephony[IN_OUT_SK]=2,Telephony[CALLS_MEDIACLASS_SK]=70634, Telephony[CALLDISPOSITION_DESC]="ALL RESERVATIONS WERE COMPLETED") - Calculate(MIN(Telephony[CALLS_TIME]),Telephony[CALLDISPOSITION_DESC]="CALLBACK REQUESTED")

+CALCULATE(AVERAGE(Telephony[CALLS_NETQTIME]),Telephony[IN_OUT_SK]=1,Telephony[CALLS_MEDIACLASS_SK]=70634, Telephony[CALLDISPOSITION_DESC]="CALLBACK REQUESTED")/86400

For the most part I am using all of the same fields with the exception of [Calls_Time] replaced [calls_beganroutingdatetime] in the second line of the staaement.

Thanks in advance for any support.

[Anonymous]

The results I am trying to get to is the total amount of time it takes once a callback has been requested and the callback has ben completed. 

[Anonymous]

Hi @Anonymous ,

It will be much easier for us to get the result if you could tell us the filter conditions and expected result.

Best Regards,

Jay

[BeaBF]

@Anonymous Hi!

Can you paste some sample data and the expected result? it would be easier...

Thx,

BF.

[Anonymous]

Here is a sample of the data. What I need to do is get the average amopunt of time it takes from Call back requested to call back completed. 

CALLS_BEGANROUTINGDATETIME	IN_OUT_SK	CALLDISPOSITION_DESC	CALLS_MEDIACLASS_SK	CALLS_NETQTIME	CALLS_TIME	COMPLETEDCALLBACK	CALLBACKREQUEUES	CALLBACKATTEMPTS
4/4/2022 00:39:31	1	ALL RESERVATIONS WERE COMPLETED	70634	6	12:39:31 AM	0	0	0
4/4/2022 03:36:10	1	ALL RESERVATIONS WERE COMPLETED	70634	6	03:36:10 AM	0	0	0
4/4/2022 05:36:37	1	ALL RESERVATIONS WERE COMPLETED	70634	7	05:36:37 AM	0	0	0
4/4/2022 05:51:32	1	ALL RESERVATIONS WERE COMPLETED	70634	694	05:51:32 AM	0	0	0
4/4/2022 06:35:18	1	ALL RESERVATIONS WERE COMPLETED	70634	7	06:35:18 AM	0	0	0
4/4/2022 06:48:58	1	HANGUP	70634		06:48:58 AM	0	0	0
4/4/2022 06:51:02	1	ALL RESERVATIONS WERE COMPLETED	70634	7	06:51:02 AM	0	0	0
4/4/2022 06:59:05	1	ALL RESERVATIONS WERE COMPLETED	70634	71	06:59:05 AM	0	0	0
4/4/2022 07:00:16	1	HANGUP	70634	0	07:00:16 AM	0	0	0
4/4/2022 07:06:55	1	ALL RESERVATIONS WERE COMPLETED	70634	7	07:06:55 AM	0	0	0
4/4/2022 07:10:47	1	ALL RESERVATIONS WERE COMPLETED	70634	8	07:10:47 AM	0	0	0
4/4/2022 07:13:01	1	ALL RESERVATIONS WERE COMPLETED	70634	10	07:13:01 AM	0	0	0
4/4/2022 07:14:43	1	ALL RESERVATIONS WERE COMPLETED	70634	6	07:14:43 AM	0	0	0
4/4/2022 07:32:47	1	ALL RESERVATIONS WERE COMPLETED	70634	7	07:32:47 AM	0	0	0
4/4/2022 07:33:01	1	ALL RESERVATIONS WERE COMPLETED	70634	7	07:33:01 AM	0	0	0

[BeaBF]

@Anonymous which are the two fields between  you want to calculate the average? CALLS_BEGANROUTINGDATETIME and....?

BF.

[Anonymous]

Based on the qlik script there is only one column CALLS_BEGANROUTINGDATETIME but there are conditions or filters. I have tried writing it like this to make it easier for me. It might help.

Measure =

VAR _Response =CALCULATE(MIN(Telephony[CALLS_BEGANROUTINGDATETIME]),Telephony[IN_OUT_SK]=2,Telephony[CALLS_MEDIACLASS_SK]=70634, Telephony[CALLDISPOSITION_DESC]="ALL RESERVATIONS WERE COMPLETED")VAR _Start =CALCULATE(MIN(Telephony[CALLS_TIME]),Telephony[CALLDISPOSITION_DESC]="CALLBACK REQUESTED")

VAR _QTime =CALCULATE(AVERAGE(Telephony[CALLS_NETQTIME]),Telephony[IN_OUT_SK]=1,Telephony[CALLS_MEDIACLASS_SK]=70634, Telephony[CALLDISPOSITION_DESC]="CALLBACK REQUESTED")/86400

VAR _AHTCALC = _Response / _Start + _QTime

--MTTR Formatted =

VAR MTTR =_AHTCALC

VAR Hours = TRUNC ( MTTR )

VAR MinSec = FORMAT ( MAX ( MTTR - Hours, 0 ) / 24, "h:nn:ss" )

RETURN

IF ( NOT ISBLANK ( _AHTCALC ), Hours & MinSec )