how to create aggregate measures

[former_member214617]

Hi guys

need to calculate no of start and end dates group by month, city...

how can i create aggregate object in universe.

data is like this:-

id start_dt end_dt city

101 2010-01-01 2010-01-10 abc

101 2010-03-10 2010-04-10 abc

102 2010-03-11 2010-03-10 abc

103 2010-04-01 2010-05-01 abc

result expected

year month start_count end_count city

2010 01 1 1 abc

2010 02 0 0 abc

2010 03 2 1 abc

2010 04 1 1 abc

2010 05 0 1 abc

[Former Member · Accepted solution]

HI,

try this;

make objects in Universe like:

YEAR: Name: Year ; Type: Number; Selct: {fn year(Reservations.res_date)}

Month: Name: Month ; Type: Number; Selct: {fn month(Reservations.res_date)}

Start count: Name: Start Count ; Type: Number; Selct: {fn Dayofmonth(Reservations.res_date)}

End count: Name: End Count ; Type: Number; Selct: {fn Dayofmonth(Reservations.res_date)}

All the best

Praveen

[Former Member]

Hi,

Create the required objects such as Year and Month.

And then create a aggregate measure such as Start Count and End Count. Define these objects as Measure. So aggregation will be applied. Set the projection such as sum or min or count in properties.

Hope this helps!