Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
So, essentially the users can change the order of the dimension is what you are saying, right?
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).
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))))
Check my response at the very bottom
This is how you can get the ObjectID in Qlik Sense