# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Talk to Experts, a LIVE Q&A Webinar. Bring your Qlik Sense Business questions on Aug. 4th. Register
cancel
Showing results for
Did you mean:
Highlighted
Partner

## Sum() Group by work around / Invalid Expression

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,

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"
;
[%%Date ID]																				AS TempDateID,
Year(Date("%%Date ID")) & Month(Date("%%Date ID")) 										AS "%%Calendar ID",
"Hotel Name",
Country,
[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

Labels (3)

• ### sum

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary

## Re: Sum() Group by work around / 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";
[%%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

Highlighted
MVP & Luminary

## Re: Sum() Group by work around / 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";