topic Re: Display Total Minutes in QlikView

Display Total Minutes

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have a table that has the following expressions: Seconds and Minutes[( Seconds/60)]

I am struggling with:

- I only want to display Seconds for each month and calculate Total Seconds and only display calculated Total Minutes next Total Seconds as per below attachment:

Re: Display Total Minutes

Anil_Babu_Samineni — Thu, 25 Jan 2018 00:49:51 GMT

Usually we can create using interval function or simply date function to resolve. But, your image is very low sresolution.

Re: Display Total Minutes

Anonymous — Thu, 25 Jan 2018 06:56:26 GMT

Thank you for your response Anil Babu, I hope you can see the image better now. Would you kindly show me how to do this interval/date function?

Re: Display Total Minutes

Ben_P — Thu, 25 Jan 2018 09:37:51 GMT

It's difficult to tell what you want from that image, but if you're looking for a way to calculate whole minutes (e.g. 90 seconds is 1.5 minutes but 1 whole minute) then you should use the DIV function. It's also worth looking at MOD for getting the remainder.

Div - script and chart function ‒ QlikView

Mod - script and chart function ‒ QlikView

Re: Display Total Minutes

Anonymous — Thu, 25 Jan 2018 11:45:49 GMT

Hi Ben,

I have been trying to reattach the image but with no success. I need to create an Expression for Total Minutes but when I do so it is duplicated across all the columns(months) but all I need is just the Total at the end as per attachment

Re: Display Total Minutes

Anonymous — Thu, 25 Jan 2018 11:51:21 GMT

Hi Ben, I have been trying to reattach the image but with no success. I need to create an Expression for Total Minutes ( I have Months : May to July an within those months I have Rev Class TB1 to TB3 - These Rev Classes are in Seconds. So I want to convert these in to minutes which is (value/60). Now my problem is that if I do so the Total Minutes expression is duplicated across all months and all I need is Total of all these months as Total Minute at the end).

Re: Display Total Minutes

Anil_Babu_Samineni — Thu, 25 Jan 2018 17:06:32 GMT

Now i am clear about your needed. You need to add extra static names can be extended.

Re: Display Total Minutes

Ben_P — Fri, 26 Jan 2018 08:36:23 GMT

You can use Dimensionality(). The value Dimensionality()=0 refers to the totals row.

So in your expression, instead of =sum(Seconds), you can use:

=if(dimensionality()=0,sum(Seconds)/60,sum(Seconds))

This will convert seconds to minutes for the totals row only, and leave everything else as seconds.

Re: Display Total Minutes

Anonymous — Mon, 29 Jan 2018 08:19:06 GMT

Hi Ben,

This works but I have multiple columns still with blanks. Do you mind showing me an example?

Regards,

Motshabi

Re: Display Total Minutes

Ben_P — Mon, 29 Jan 2018 10:04:34 GMT

Hi Motshabi, example below.

You will also need to tick the box 'show partial sums' under the 'presentation' tab of the table.

Re: Display Total Minutes

Ben_P — Mon, 29 Jan 2018 12:48:58 GMT

Here's the qvw if you need it

Re: Display Total Minutes

Anonymous — Mon, 29 Jan 2018 13:20:51 GMT

Hi Ben,

Please find the example attached. I have tried Dimensionality but it still doesn't work.

Re: Display Total Minutes

Ben_P — Mon, 29 Jan 2018 13:50:59 GMT

Dimensionality() refers to the vertical dimensions (columns) of the table.

For a pivot table that is displaying totals by both row and column, you also need to use SecondaryDimensionality(), which refers to the horizontal dimensions (rows) of the table.

So basically we are saying for anything that is in either the totals row or the totals column, calculate seconds/60, otherwise seconds.

I've done this for you in the attached file. The formula is:

=if(Dimensionality()=0 or SecondaryDimensionality()=0,sum(Seconds)/60,sum(Seconds))

Re: Display Total Minutes

Anonymous — Mon, 29 Jan 2018 14:02:51 GMT

Thank you so much for your time Ben, this is what I was looking for

Re: Display Total Minutes

Ben_P — Mon, 29 Jan 2018 14:04:15 GMT

Happy to help