Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Ocupation table

Hi!   I have a table with reservations. I have check in date and number of nights.   I want to create a visual with the ocupation each day in the hotel, for example a bar chart. To get this i wan...
Need Help
  • PattemManohar's avatar
    PattemManohar
    7 years ago

    Anonymous  Please try this as a New Table to achieve your desired output i.e. Output 2 as mentioned above.

     

    Test304Out = 
VAR _Date = SELECTCOLUMNS(GENERATE(CALENDAR(MIN(Test304RowSplit[CheckIn]),MAX(Test304RowSplit[CheckIn])),Test304RowSplit),"Date",[Date],"ID",[ID],"CheckOut",Test304RowSplit[CheckIn]+(Test304RowSplit[Nights]-1),"ExcludeFlag",IF([Date]>=Test304RowSplit[CheckIn] && [Date]<=Test304RowSplit[CheckIn]+(Test304RowSplit[Nights]-1),"N","Y"))
RETURN SELECTCOLUMNS(FILTER(_Date,[ExcludeFlag]="N"),"Date",[Date],"ID",[ID])

PattemManohar's avatar
PattemManohar
Icon for Community Champion rankCommunity Champion
7 years ago

Anonymous  Just to confirm - The above table is your expected output based on the input table (which contains checkin data and no. of nights booked). Could you please post sample test input data and assuming the above table is your expected output (Date and Rooms Occupated)

Anonymous's avatar
Anonymous
Not applicable
7 years ago

PattemManohar  Here you have what you asked for.  The output 2 is the desired but i conform with output 1 too

 

  • PattemManohar's avatar
    PattemManohar
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Anonymous  Please try this as a New Table to achieve your desired output i.e. Output 2 as mentioned above.

     

    Test304Out = 
VAR _Date = SELECTCOLUMNS(GENERATE(CALENDAR(MIN(Test304RowSplit[CheckIn]),MAX(Test304RowSplit[CheckIn])),Test304RowSplit),"Date",[Date],"ID",[ID],"CheckOut",Test304RowSplit[CheckIn]+(Test304RowSplit[Nights]-1),"ExcludeFlag",IF([Date]>=Test304RowSplit[CheckIn] && [Date]<=Test304RowSplit[CheckIn]+(Test304RowSplit[Nights]-1),"N","Y"))
RETURN SELECTCOLUMNS(FILTER(_Date,[ExcludeFlag]="N"),"Date",[Date],"ID",[ID])