Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys im currently working with sum() and group by but i am stuck coz i need to include a specific field in the group by that i don''t need to include in the grouping is there a way to or work around on it...
Load
"%%Calendar ID",
"Hotel Name",
"%%MonthYearHotelID",
Country,
"Guarantee/ Buyout Excess % Split",
If(Year(Date(TempDateID)) = Year(Now()) AND Month(Date(TempDateID)) >= Month(Now()),
(Sum([Net Revenue]) / (Day(Now()) -1) )* Day(MonthEnd(Now())),Sum([Net Revenue]))
AS "Extrapolated Net Revenue for Plus Calc"
group by "%%Calendar ID", "Hotel Name", Country, "%%MonthYearHotelID","Guarantee/ Buyout Excess % Split"
;
Load
[%%Date ID] AS TempDateID,
Year(Date("%%Date ID")) & Month(Date("%%Date ID")) AS "%%Calendar ID",
"Hotel Name",
Country,
"Guarantee/ Buyout Excess % Split",
[Net Revenue],
Month(Date([%%Date ID])) & '-' & Year(Date([%%Date ID])) & '-' & "Hotel Name" AS "%%MonthYearHotelID"
Resident Orders
Notice that i did't include the TempDateID in the group by coz i dont want it to be part of the Aggregation... but that causes me an error it says invalid expression
You could do the check with the TempDateID one step before, maybe with something like this:
Load
"%%Calendar ID", "Hotel Name", "%%MonthYearHotelID",
Country, "Guarantee/ Buyout Excess % Split",
Sum([Net Revenue]) / avg(FLAG) AS "Extrapolated Net Revenue for Plus Calc"
group by "%%Calendar ID", "Hotel Name", Country, "%%MonthYearHotelID","Guarantee/ Buyout Excess % Split";
Load
[%%Date ID] AS TempDateID, Year(Date("%%Date ID")) & Month(Date("%%Date ID")) AS "%%Calendar ID",
"Hotel Name", Country, "Guarantee/ Buyout Excess % Split", [Net Revenue],
If(Year(Date(TempDateID)) = Year(Now()) AND Month(Date(TempDateID)) >= Month(Now()),
(Day(Now()) -1) / Day(MonthEnd(Now())), 1) as FLAG,
Month(Date([%%Date ID])) & '-' & Year(Date([%%Date ID])) & '-' & "Hotel Name" AS "%%MonthYearHotelID"
Resident Orders
- Marcus
You could do the check with the TempDateID one step before, maybe with something like this:
Load
"%%Calendar ID", "Hotel Name", "%%MonthYearHotelID",
Country, "Guarantee/ Buyout Excess % Split",
Sum([Net Revenue]) / avg(FLAG) AS "Extrapolated Net Revenue for Plus Calc"
group by "%%Calendar ID", "Hotel Name", Country, "%%MonthYearHotelID","Guarantee/ Buyout Excess % Split";
Load
[%%Date ID] AS TempDateID, Year(Date("%%Date ID")) & Month(Date("%%Date ID")) AS "%%Calendar ID",
"Hotel Name", Country, "Guarantee/ Buyout Excess % Split", [Net Revenue],
If(Year(Date(TempDateID)) = Year(Now()) AND Month(Date(TempDateID)) >= Month(Now()),
(Day(Now()) -1) / Day(MonthEnd(Now())), 1) as FLAG,
Month(Date([%%Date ID])) & '-' & Year(Date([%%Date ID])) & '-' & "Hotel Name" AS "%%MonthYearHotelID"
Resident Orders
- Marcus