Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
vizaac
Contributor II
Contributor II

Group by date and time to calculate total count

Dear Community members,

I have just started to use Qlik Sense's to see its capabilities. So I can say that I am a newbie.  

I have a log table which has Date(DD/M/YYYY) and Time(hh:mm:ss) column. 

1. I would like to produce minutes level (hh:mm) Time column

to calculate  

2. Total count of frequency grouped by Date and new Time(hh:mm) columns

Would you please help me (if it is possible) to generate this report?

Regards,    

Labels (1)
5 Replies
brunobertels
Master
Master

dimension new time 

time(floor('23:56:45',maketime(0,1)),'hh:mm') will give you 23:56 (in fact 23:56:00 even if seconds are 02 , 35 , 56 or what else)

 

replace '23:56:45' by your time dimension 

hope it helps 

vizaac
Contributor II
Contributor II
Author

Thank you very much for your time and return. 

The formula you have shared with me converts hh:mm:ss to hh:mm just for one single value ('23:56:45'). Is it possible to use a formula I can apply for whole column? 

And do you have any idea to calculate frequency count of Date and this new Time Column? 

All the best,  

vizaac
Contributor II
Contributor II
Author

Dear brunobertels, I have adopted your formula to my case as follows: 

time(floor(Time(Frac(Timestamp#(Left([Log Tarihi [date1]]],21),'DD/M/YYYY hh:mm:ss tt')),'hh:mm:ss'),maketime(0,1)),'hh:mm')

And it worked. 

Now, I solved the first item of my question. 

But Question number 2 remains unresolved:

2. Total count of frequency grouped by Date and new Time(hh:mm) columns

Thank you, 

brunobertels
Master
Master

may be this 

 

sum(

aggr (count(ssdd),LogDate,ssdd)

)

vizaac
Contributor II
Contributor II
Author

Dear @brunobertels, 

Thank you very much again for your help. But I need to clarify some points in order to test your code for my app.

I have attached visual output of my table in my app. As you can see in the visual, the only "field" in this table is : Log Tarihi [date1]. The other columns are labels which I gave for my expressions. I have coded expressions for the fields and I gave a label name for each field. You can see two column definitions in the screen output file.

I wonder if there is any chance to use labels in expressions. If not, how can I form your formula? 

Regards,