Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

eustisab
New Contributor III

return the value that matched the category.

Hi Everyone,

I'm really new in Qlik. would like to return the data with the below conditions.

I have attached the data with 2 tabs:

1. sample data

2. desired result

Condition:
Return CLI for 1st Participant "customer" Purpose INBOUND Direction
Return CLI for "customer" Participant Purpose OUTBOUND Direction

luismadriz‌ thanks!

1 Solution

Accepted Solutions
luismadriz
Valued Contributor

Re: return the value that matched the category.

Hi Isabel,

Please create a straight table with all the fields in the desired result as Dimensions and only CLI as a measure with the following

=FirstSortedValue( DISTINCT {<[Participant Purpose]={customer},[Conversation Direction]={Inbound,Outbound}>} CLI, Date)

You should get the desired result. I just tried it and it worked

Regards,

Luis

Untitled.png

13 Replies
luismadriz
Valued Contributor

Re: return the value that matched the category.

Hi Isabel,

Please create a straight table with all the fields in the desired result as Dimensions and only CLI as a measure with the following

=FirstSortedValue( DISTINCT {<[Participant Purpose]={customer},[Conversation Direction]={Inbound,Outbound}>} CLI, Date)

You should get the desired result. I just tried it and it worked

Regards,

Luis

Untitled.png

eustisab
New Contributor III

Re: return the value that matched the category.

Hi Luis,

The AHT, ATT, Hold and ACW there are measures.

I'll try if this will still work. (fingers crossed)

=FirstSortedValue( DISTINCT {<[Participant Purpose]={customer},[Conversation Direction]={Inbound,Outbound}>} CLI, Date)

eustisab
New Contributor III

Re: return the value that matched the category.

Everything were deleted

cli.JPG

luismadriz
Valued Contributor

Re: return the value that matched the category.

Ok, for each of those measures update as per the following example

Sum({<[Participant Purpose]={customer},[Conversation Direction]={Inbound,Outbound}>} ACW)

Please let me know,

Luis

eustisab
New Contributor III

Re: return the value that matched the category.

I need to change the ACW on this expression.

Sum({<[Participant Purpose]={customer},[Conversation Direction]={Inbound,Outbound}>}ACW)


ACW on my measure is

num((sum([Aggr tAcw]/1000)/sum([Aggr nHandle])),vFormatSeconds2)


when I use this, it's giving me Error.

Sum({<[Participant Purpose]={customer},[Conversation Direction]={Inbound,Outbound}>}num((sum([Aggr tAcw]/1000)/sum([Aggr nHandle])),vFormatSeconds2))


I'm so sorry if i still need to ask you this. I'm just really new.

luismadriz
Valued Contributor

Re: return the value that matched the category.

Try this:

num((sum({<[Participant Purpose]={customer},[Conversation Direction]={Inbound,Outbound}>} [Aggr tAcw]/1000)/sum({<[Participant Purpose]={customer},[Conversation Direction]={Inbound,Outbound}>} [Aggr nHandle])),vFormatSeconds2)

eustisab
New Contributor III

Re: return the value that matched the category.

Hi Luis,

The highlighted is not showing the correct data.

it should be returning this CLI instead

+61383634860

cliv2.JPG

Highlighted
luismadriz
Valued Contributor

Re: return the value that matched the category.

Good you have most of it working,

Without a proper field to determine which one goes first it won't be possible.

Ideally an order field should be placed in the the data. As those calls happened at the same date and at the same time, there is no way to confirm for sure.

My suggestion is that you try to get to the load script to add a field in the data that could represent the load order especially for those calls that happened the same date and the same time

I hope this helps,

Cheers,

Luis

PS. When applicable please replies as Helpful if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as Helpful if you feel additional info is useful to others

eustisab
New Contributor III

Re: return the value that matched the category.

Thanks Luis!

Can I still get back to you once I have placed the additional data field for load order?