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: 
Not applicable

Calculate number of minutes in YTD

Hi, I have posted a previous post on how do i calculate the days in YTD and im still stuck but also now i need to know the minutes in YTD in order to calculate the percentage theatre utilisation in a hospital. i have a field to caculate the actual minutes used in a theatre and my set analysis on this are. TheatreTotaltime is the actual minutes that the theatres were used. this i must used to calculate the percentage utilisation of the theatres

=



Sum({$<Year={"$(=Year(Today()))"},Month={">=01 <=$(=Num(Month(Today()))-1)"}>} TheatreTotalTime)

Now i need the actual minutes for YTD to calculate the percentage usage of the theatres.

Would really appreciate help how to calculate this percentage.

Regards

Louw

1 Solution

Accepted Solutions
matt_crowther
Luminary Alumni
Luminary Alumni

Louw,

Give this a try:

=

num(daynumberofyear(monthstart(today())-1)*24*60,'##,###')

We're essentially finding the start date of the current month we're in and then taking '1' from it to give us the day number of the last day of last month...and then we do the hours and minutes multiplication.

Hope that solves it.

Matt - Visual Analytics Ltd



View solution in original post

10 Replies
matt_crowther
Luminary Alumni
Luminary Alumni

Give this a try:

=num((DayNumberOfYear(now())-1)*24*60+(Hour(now())*60)+minute(now()),'##,###')

First section works out the minutes up until midnight yesterday, then how many minutes in the completed hours for today and finally how many minutes in the current hour.

Be warned this is using the now() function and could well lead to performance issues - I'd recommend using today() to limit the processing impact as now() needs to be recalculated every second as the time changes.

Hope that helps,

Matt - Visual Analytics Ltd

Not applicable
Author

Hi Matt,

Thanks for the reply and i did test it and it seems its is going to be my solution with a slight modification. There is just one hickup, the client see YTD always as up to the last completed month. For instance if it is middle march now and we load data today then ytd will be to the end of Feb. This means that i must calculate the minutes up to end of Feb. Any idea on this.

thks so much for your help.

Louw

matt_crowther
Luminary Alumni
Luminary Alumni

Louw,

Give this a try:

=

num(daynumberofyear(monthstart(today())-1)*24*60,'##,###')

We're essentially finding the start date of the current month we're in and then taking '1' from it to give us the day number of the last day of last month...and then we do the hours and minutes multiplication.

Hope that solves it.

Matt - Visual Analytics Ltd



Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

I think it can be as simple as this:

(MonthStart(today()) - YearStart(today())) * 24 * 60

Not applicable
Author

Mat thanks your suggestion it worked and is solving my YTD minutes calculation.

My client want to see utilization now for last completed month if it is the 14 march today they want to see the utilization of the last completed month's theatre usage. Now i must calculate the minutes for last month and this will be now february. daynumberofyear is not going to work here is there a function like daynumberofmonth or simular i need now to calculate the total minutes for last completed month.

Thank so much for your help

Louw

matt_crowther
Luminary Alumni
Luminary Alumni

Give this a try:

=num(day(monthend(addmonths(today(),-1)))*24*60,'##,###')

Hope that helps,

Matt - Visual Analytics Ltd

Not applicable
Author

Mat thank you for your help it worked 100%

Regards

Louw

Not applicable
Author

Hi Mat,

You gave me the solution to calculate the number of minutes for year to date

num(daynumberofyear(monthstart(today())-1)*24*60,'##,###')



By using this i can also caculate the number of days by just taking away the * 24 and the * 60 and it worked 100% for me, but now i need to calculate the number of days in the fiscal year to date. This will now be the number of days from 1 Oct 2010 up to the last day of the last completed month that is Feb 2011.

Do you have and idea how to calculate this.

Your help is appreciated.

Regards

Louw

matt_crowther
Luminary Alumni
Luminary Alumni

Should be straight forward: take the formula you have for the number of days in this calendar to date and then add 92 - 31 Days in Oct + 30 Days in Nov + 31 Days in Dec.

num(daynumberofyear(monthstart(today())-1)+92

(Un-checked syntax).

The days between 01 Oct 2010 and the start of 2011 will never change so it's simply adding 92.

Hope that helps,

Matt - Visual Analytics Ltd