Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
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,
    "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  

Labels (3)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

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

 

View solution in original post

1 Reply
MVP & Luminary
MVP & Luminary

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

 

View solution in original post