Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good morning,
I have a field which indicates the number of rooms available in a hotel. For each record in the BBDD the numbers of rooms is repeated obviously. In order to know the number of rooms for any period I use =avg(inventory), so if one day the availables rooms were 528 it indicates so, and the same for one month, year..., 528.
Is correct that the inventory for one day is 528 rooms but if the selected period is, for example, 'jan' the correct inventory should be 528x31 if the period were year=2012 the correct inventory should be 528x366, etc.
I suppose exists a function to do something like vInventori=avg(inventori) x periodduration but I was not able to find it in any help or QV manual (as usual) I wonder if I am the only one that this happens.
Thanks
Hi,
Yes it does:
Day(MonthEnd(Fecha)) AS DiasMes
Will return the number of days in the month within the field Fecha
Hope that helps.
Miguel
Hi,
Based on your condition, you need to multiply the number of days based on the first day of your data up to selected month and year. Is this correct? Do you have a date field in your table?
Regards,
Janzen
Hi,
yes I have a field with all the dates from n years ago to two moths forward in future, and a master calendar to treat them as individual month days (1,2,3...31), week days (mon,tue,...), individual months (jan, feb,...) , individual month-years (jan-2011, feb-2011) and so on.
I use the master calendar to filter the data in the document, I work basically with entire moths but when I refer to the current month I need consider from monthstart to today in order to calculate inventory.
Regards
The way I intend to use the dates is something like this:
SELECTED HOTEL ROOMS = 528 | 528 | |||||
YEAR = 2012 | ||||||
INVENTORY | ||||||
countrys | JAN | FEB | MAR | JAN | FEB | MAR |
CANADA | 528X31 | 528X29 | 528X31 | 16368 | 15312 | 16368 |
UK | 528X31 | 528X29 | 528X31 | 16368 | 15312 | 16368 |
FRANCE | 528X31 | 528X29 | 528X31 | 16368 | 15312 | 16368 |
AUSTRALIA | 528X31 | 528X29 | 528X31 | 16368 | 15312 | 16368 |
OCCUPPIED ROOMS | ||||||
countrys | JAN | FEB | MAR | |||
CANADA | 4000 | 2500 | 2700 | |||
UK | 2000 | 3500 | 5300 | |||
FRANCE | 6000 | 5500 | 1500 | |||
AUSTRALIA | 1000 | 2000 | 4200 | |||
%OCCUPPANCY | ||||||
countrys | JAN | FEB | MAR | |||
CANADA | 24% | 16% | 16% | |||
UK | 12% | 23% | 32% | |||
FRANCE | 37% | 36% | 9% | |||
AUSTRALIA | 6% | 13% | 26% | |||
TOTAL | 79% | 88% | 84% |
Hi,
Maybe you could try this one.
avg({<Year = {$(=only(Year))}, Month = {">min(date)<max(date)"} >} inventory)*Day(MonthEnd(date))
Regards,
Janzen
Thank you, but It's too complicated for my kview knowledge.
I think I could solve my problem in the load sript simply adding a filed with the number of the month days ¿ is there any funtion which gives numbre of days for a month (JAN=31, FEB=28, MAR=31,...)?
My idea is doing something like
load
numberofdays(month(DATE)) as daysopen
from...
¿does it exist a funtion like numberofdays?
Thanks
Hi,
Yes it does:
Day(MonthEnd(Fecha)) AS DiasMes
Will return the number of days in the month within the field Fecha
Hope that helps.
Miguel
Thank you