Date Dimension Aggr(max)) Repeating

[cbaqir]

I thought I had figured out the syntax for my date dimension because it looks right when one DEFECT_ID is selected but when multiple are selected, the date is repeating instead of showing aggregated by month. Can someone please help?

=aggr(max( date(DEFECT_STATUS_START_DATE,'MMM-YYYY')),DEFECT_ID)

[stigchel]

The function

date(DEFECT_STATUS_START_DATE,'MMM-YYYY')

Only formats the date. The date is a dual where the number part will still reflect each individual date. To have a single date per month you can use MonthStart(Date.....

[stigchel]

See also

The Date Function

[cbaqir]

Is there a way to show MonthStart as MMM-YYYY?

[stigchel]

Sure, should have turned that around myself already, so just wrap the formatting date function around the monthstart function instead of the other way around

[YoussefBelloum]

Hi,

I think this calculated dimension will help get the expected output

=Month(DEFECT_STATUS_START_DATE)&Year(DEFECT_STATUS_START_DATE)

[stigchel · Accepted solution]

Dimension

=aggr(max( date(Monthstart(DEFECT_STATUS_START_DATE),'MMM-YYYY')),DEFECT_ID)