Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

jorditorras
Contributor

Count minutes per Day/hour/Month,etc

Dear Gurus,

I'm struggling to find a calculation that seems easy but I cannot find our how to reach it.

I have a pivot table with some Time dimensions (Month, Day, hours, etc) and some KPI's. I would like to add one KPI in this table to show me the total of minutes of my time selection. So, If I select "Day", I'd see in each colum: 1440 (24hours x 60 minutes). If I select Hour, I'd have to see 60*Number of selected Days. If I select Month, I'd have to see 43200 (30days -if that month has 30 days- x 24 hours x 60 minutes).

How can I reach this calculation? Thanks in advance!

Regards,

Jordi

25 Replies

Re: Count minutes per Day/hour/Month,etc

Do you have a field in which you have Day, Hour, Month as possible selections and you wish to see 1440 minutes when you select Day? Is that what the goal is?

jorditorras
Contributor

Re: Count minutes per Day/hour/Month,etc

Yes, If I display the pivot table per day, I want to see 1440 (as it's the total of minutes in one day). If I display it per Year-Month, I want to see 43200 (as it's the total of minutes in one Year-Month). If I display the table per Hour and I filter, for example 7AM and two days, I want to see 120 (60 minutes per two days at 7AM).


I hope I explaining clear enough....


Thank you!!


Regards,

Jordi

Re: Count minutes per Day/hour/Month,etc

I am not completely sure I understand how your data is.... would you be able to share a sample or some sample data to understand your data to give you a response which might help you

jorditorras
Contributor

Re: Count minutes per Day/hour/Month,etc

Wow, I didn't see this last response... I attach some examples. Minutes Calculation.PNG

   I'd need the calculation in Orange. So then I can Calcuate the Minutes of activity VS the Total of minutes.

Thanks for your help!!

Regards,

Jordi

Re: Count minutes per Day/hour/Month,etc

If you have Hour as the most granular data in your dataset, you can just add another column in your data set

LOAD MonthYear,

           Date,

           Hour,

           60 as Minutes

FROM ....;

and then you can just do Sum(Minutes)

This should work as long as you have a master calendar with all date and all times. or you have a master calendar with all dates and a master time table with all times.

pradosh_thakur
Honored Contributor II

Re: Count minutes per Day/hour/Month,etc

are you using the alternate dimensions . if yes then

try using GetObjectField ([index]) with if/pick-match to show number of minutes in the UI

something similar to

if(GetObjectField ([index])=dim1 ,14400,if(GetObjectField ([index])=dim2,43200,if(GetObjectField ([index])=dim3,60)))

have not tested the code.please tweak if needed.

regards

Pradosh

Learning never stops.
jorditorras
Contributor

Re: Count minutes per Day/hour/Month,etc

Hi Pradosh,

I'm using a pivot table and I can pick alternate dimensions. I have tried to use your formula as follows:

if(GetObjectField (1)=[Date] ,14400,if(GetObjectField (1)=[Month-Year_EN],43200,if(GetObjectField (1)=[Hour],60)))

Althought in the definition of the formula, there's no error, the result given is "-"

Thanks you very much for your help.

Regards,

Jordi

jorditorras
Contributor

Re: Count minutes per Day/hour/Month,etc

Hi Sunny,

The most granular data I have is time (hh:mm:ss). Generally I have one record per minute, but ocasionally, the production stops and there are some specifics minutes when I don't have any record.

I have tried to add "60 as minutes" in my master calendar but then in the pivot table when I use Sum(Minutes) the result displayed is wrong...

Thanks for your help Sunny!

Regards,

Jordi

Re: Count minutes per Day/hour/Month,etc

Would you be able to share few rows of data to show how your data looks like. It might be easier to work with some data which makes sense....

Community Browser