topic Re: Duration for a selected period in QlikView

Duration for a selected period

Tue, 09 Oct 2012 06:59:07 GMT

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

Re: Duration for a selected period

Tue, 09 Oct 2012 07:25:09 GMT

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

Re: Duration for a selected period

Tue, 09 Oct 2012 07:42:11 GMT

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

Re: Duration for a selected period

Tue, 09 Oct 2012 07:53:48 GMT

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%

Re: Duration for a selected period

Tue, 09 Oct 2012 09:33:09 GMT

Hi,

Maybe you could try this one.

avg({<Year = {$(=only(Year))}, Month = {">min(date)<max(date)"} >} inventory)*Day(MonthEnd(date))

Regards,

Janzen

Re: Duration for a selected period

Tue, 09 Oct 2012 11:28:30 GMT

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

Re: Duration for a selected period

Miguel_Angel_Baeyens — Tue, 09 Oct 2012 11:30:31 GMT

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

Re: Duration for a selected period

Tue, 09 Oct 2012 11:35:56 GMT

Thank you