25 Replies Latest reply: Dec 28, 2017 11:01 AM by Sunny Talwar

# 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

• ###### 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?

• ###### 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

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

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.

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

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

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

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

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

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

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

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

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

Hi Sunny,

Yes, my screenshot is a Pivot table, but I have the Measures in Rows and Dimensions in columns. I've tryed what you posted, and it works fine, but the problem appears when I change de dimension. If I, for example, select "Day" as the first dimension instead of "Month". Then it's showing the same result as if "month" was the first one.

Thank you so much anyway for this useful colaboration.

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

So, essentially the users can change the order of the dimension is what you are saying, right?

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

Yes, the table can be displayed by Day, Month, Hour, etc in the first column. The user has to be free to choose it (that's why I chose Pivot Table instead Straight Table). User will be able whether to drilldawn dimensions or analyze the result by one selected single dimension (Month, day, etc).

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

Check my response at the very bottom

• ###### 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

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

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

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

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

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

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

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

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

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

Here is hybrid solution to change in order of dimension by users

=Pick(Dimensionality(),

If(GetObjectField(0, 'CH01') = 'MonthYear', 30*24*60,

If(GetObjectField(0, 'CH01') = 'Date', 24*60,

If(GetObjectField(0, 'CH01') = 'Hour', 60))),

If(GetObjectField(1, 'CH01') = 'MonthYear', 30*24*60,

If(GetObjectField(1, 'CH01') = 'Date', 24*60,

If(GetObjectField(1, 'CH01') = 'Hour', 60))),

If(GetObjectField(2, 'CH01') = 'MonthYear', 30*24*60,

If(GetObjectField(2, 'CH01') = 'Date', 24*60,

If(GetObjectField(2, 'CH01') = 'Hour', 60))))