topic Re: Group by is not working in QlikView

Group by is not working

ngreddy1982 — Thu, 18 Aug 2022 12:29:04 GMT

Hi All,

I am trying to do group by Year which is derived from Date, for some reason, it is not working as expected, getting the same number of rows with date Vs Year. Please advice how can i group the below by Year

TestStg:

LOAD
BrachNumber
,Date(Date#(SoldDateKey,'YYYYMMDD'),'YYYY') AS [SoldDateYear]
,SUM([Unbundled Charge Total Amount]) AS [Unbundled Charge Total Amount]

From Sale.QVD (qvd)

GROUP BY
BrachNumber
,Date(Date#(SoldDateKey,'YYYYMMDD'),'YYYY');

Thank you

Re: Group by is not working

Or — Thu, 18 Aug 2022 12:35:59 GMT

Neither Date#() nor Date() change the underlying (numeric) date value, only the way it is formatted. If you're trying to isolate the year from a date try Year(Date) instead. Alternatively, pick out the left(Date,4) from your original text value.

Re: Group by is not working

ngreddy1982 — Thu, 18 Aug 2022 13:01:58 GMT

Hi,

can you please explain with an example?