topic Re: Count minutes per Day/hour/Month,etc in App Development

Count minutes per Day/hour/Month,etc

jorditorras — Thu, 14 Dec 2017 16:53:53 GMT

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

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

sunny_talwar — Thu, 14 Dec 2017 16:55:45 GMT

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?

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

jorditorras — Thu, 14 Dec 2017 17:12:44 GMT

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

sunny_talwar — Fri, 15 Dec 2017 10:39:19 GMT

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

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

jorditorras — Wed, 27 Dec 2017 17:14:21 GMT

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

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

sunny_talwar — Wed, 27 Dec 2017 17:26:20 GMT

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.

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

pradosh_thakur — Wed, 27 Dec 2017 18:33:42 GMT

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

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

jorditorras — Thu, 28 Dec 2017 11:52:44 GMT

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

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

jorditorras — Thu, 28 Dec 2017 12:04:39 GMT

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

sunny_talwar — Thu, 28 Dec 2017 12:06:50 GMT

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

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

pradosh_thakur — Thu, 28 Dec 2017 12:15:44 GMT

what about

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

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

jorditorras — Thu, 28 Dec 2017 12:16:57 GMT

Yes, I attach an screenshot with how data looks like. The last KPI in the screenshot is the one I tried with Sum(Minutes).

Thanks,

Regards,

Jordi

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

jorditorras — Thu, 28 Dec 2017 12:28:08 GMT

I have tried this. Now in all my selections, the result shown is 60. I seems like this formula is interpretating in all cases that my selection is "Hour".

I have made a test of it by typing:

if(GetObjectField (1)='Hour' ,1,2)

Just to check the behaviour, and I see that the result is always 1 whether if I select Hour, Date, Month...

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

pradosh_thakur — Thu, 28 Dec 2017 12:32:49 GMT

add an extra column with GetObjectField (1) and check what you are getting .. you need to change the index inside the function to make it work your way . with a small tweaking in the code you can achieve this ..

a small app or a screenshot that can describe the structure and what dimension would be a plus.

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

jorditorras — Thu, 28 Dec 2017 12:36:57 GMT

I have seen that:

GetObjectField (1)='Hour'

GetObjectField (2)='Time'

GetObjectField (3)='Date'

GetObjectField (4)='Shift'

I'm going to try more possibilites with this clue...

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

pradosh_thakur — Thu, 28 Dec 2017 12:52:01 GMT

you mentioned you are using alternate dimensions. you have to tweak your code to incorporate this my friend . share the screenshot of table with a few needed details to construct it and we will be able to help you better.

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

jorditorras — Thu, 28 Dec 2017 14:47:38 GMT

Well, it's not alternate dimensions as an in a graph that it's possible to switch them (I think this is what you meant); it's a pivot table where I can choose the dimension. I attached an screenshot in my last response to Sunny.

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

sunny_talwar — Thu, 28 Dec 2017 15:10:19 GMT

Try something like this

=Pick(Dimensionality(), 30*24*60, 24*60, 60)

You might have to fix the order of this based on your order of Dimensions, but you can see what I did

Dimension order

MonthYear - > Dimensionality() = 1

Date - > Dimensionality() = 2

Hour -> Dimensionality() = 3

When MonthYear is the main dimension

When Date is expanded

When Hour is expanded

Also note that if you are using pivoted dimension (which I don't think you are from your screenshot), you will have to use SecondaryDimensionality() instead of Dimensionality()

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

pradosh_thakur — Thu, 28 Dec 2017 15:24:13 GMT

can you post a sample with dummy data and a sample output if possible.

i would try to try out a pivot with my data but it may not be the one you wanted. it would help if you could post a sample which resembles your pivot.

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

pradosh_thakur — Thu, 28 Dec 2017 15:28:09 GMT

well i was going to suggest dimensionality() when got to know the dimensions are not alternate but stalwar1‌ is too quick for me!!