Sum() Group by work around / Invalid Expression

[raceberos]

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  

[marcus_sommer · Accepted solution]

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