5 Replies Latest reply: Aug 29, 2012 6:43 AM by Florian Pennetzdorfer RSS

    GROUP BY requires ALL fields?

    Florian Pennetzdorfer

      Hello,

       

      I want to aggregate daily Sales for each Country.

       

      load     Date,
           Year,
           Quarter,
           Month,
           Week,
           Day,
           Country,
           Sum(Sales)
      resident SalesTable group by Date, Country;
      

       

      Running the script results in an "Invalid expression" error message... apparently I have to list ALL used fields in the group by clause

      Qlikview Manual:

      No other fields than the aggregation fields may be used outside aggregation functions in the loaded expressions.

       

      If I do so, it works. But in my opinion this is illogical: Why do I have to list Year, Quarter, Month, Week and Day in the group by clause although they don't affect the aggregation level (Date = lowest level of detail)?!

       

      In my real application the group by clause would then consist of about 20 fields ... is there another way to avoid that?

       

      Thx for you suggestions!