Count minutes per Day/hour/Month,etc

[jorditorras]

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

[sunny_talwar]

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]

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

[sunny_talwar]

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]

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

   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

[sunny_talwar]

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]

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

[jorditorras]

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]

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

[sunny_talwar]

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....