Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jorditorras
Creator
Creator

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
jorditorras
Creator
Creator
Author

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.

sunny_talwar

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

jorditorras
Creator
Creator
Author

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

sunny_talwar

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

sunny_talwar

Check my response at the very bottom