I'm trying to use the aggregate function but its returning like sum function, not sure what im doing wrong here. Ive tried to find a few examples online but nothing yet.
WITH
MEMBER [Measures].[StartDate] AS
Aggregate(@{Date!∅}.Lag(0), [Measures].[Net Sales])
MEMBER [Measures].[7 Days] AS
Aggregate(@{Date!∅}.Lag(7), [Measures].[Net Sales])
MEMBER [Measures].[14 Days] AS
Aggregate(@{Date!∅}.Lag(14), [Measures].[Net Sales])
MEMBER [Measures].[21 Days] AS
Aggregate(@{Date!∅}.Lag(21), [Measures].[Net Sales])
MEMBER [Measures].[Avg 6-Mth Day] AS
(
[Measures].[StartDate] + [Measures].[7 Days] + [Measures].[14 Days] + [Measures].[21 Days]
) / 4
SELECT
NON EMPTY {
[Measures].[StartDate]
, [Measures].[7 Days]
, [Measures].[14 Days]
, [Measures].[21 Days]
} ON "Columns"
NON EMPTY [Calendar].[Hour].[Hour] ON "Rows"
FROM
[ECRS Data Warehouse]
WHERE
(@{Date!∅})
Expecting to get aggregate data by hour and not summed data by hour, its like its defaulting to sum not aggregate.