Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
khajafareed
Contributor III
Contributor III

Want to know the how many times did the Caller reached for same issue within 24 hours

I have data in below format, my requirement is - i want to know if the caller reached for same Category and Sub Category issue within 24 hours of time and also how many times Caller reached to ITSD for same issue.

Please advise how can we get above requirement.

NumberCallerCategorySub CategoryCreatedHelpdesk Type
IN0114680Justin BellPhysical DeviceBreak/Fix2020-10-23 02:07:29IT Service Desk
IN0114685RobinsApplication/Orders/ResultsResults2020-10-23 01:26:19IT Service Desk
IN0114686Ashi MohAccessPassword Reset2020-10-23 00:02:01IT Service Desk
IN0114687Lucy SamuelsAccessAccount Unlock2020-10-23 00:35:45IT Service Desk
IN0114689George MilleDataData Loss2020-10-23 00:27:15IT Service Desk
4 Replies
villegasi03
Creator
Creator

There might be an easier way but here is what comes to mind. 

Load

Caller,

(Category & ' - ' & Sub Category) as Cat_SubCat,

Count(Number) as NumOfCalls

From InputData Group by Caller, (Category & ' - ' & Sub Category) ;

This should give you a count the number of calls by category & sub category. 

villegasi03
Creator
Creator

sorry forgot the part about the same day. 

villegasi03
Creator
Creator

Caller,

Date(Floor(Created), 'mm/dd/yyyy') as CreatedDt,

(Category & ' - ' & Sub Category) as Cat_SubCat,

Count(Number) as NumOfCalls

From InputData Group by Caller, Date(Floor(Created), 'mm/dd/yyyy'), (Category & ' - ' & Sub Category) ;

khajafareed
Contributor III
Contributor III
Author

Thanks for Response, i tried but got invalid expression error. Also i would like to know the caller details as well.

LOAD Number,
Count(Number) as NumOfCalls,
Caller,
Category,
[Sub Category],
(Category & ' - ' & [Sub Category]) as Cat_SubCat,
Created,
Date(Floor(Created), 'mm/dd/yyyy') as CreatedDt,
[Helpdesk Type]
FROM [C:\Users\Dev\Downloads\interaction.xlsx] (ooxml, embedded labels, table is [Page 1])
Group by Caller, Date(Floor(Created), 'mm/dd/yyyy'), (Category & ' - ' & [Sub Category]) ;