topic Re: Date Dimension Aggr(max)) Repeating in QlikView

Date Dimension Aggr(max)) Repeating

cbaqir — Mon, 17 Sep 2018 14:52:12 GMT

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)

Re: Date Dimension Aggr(max)) Repeating

stigchel — Mon, 17 Sep 2018 14:59:04 GMT

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.....

Re: Date Dimension Aggr(max)) Repeating

stigchel — Mon, 17 Sep 2018 15:03:15 GMT

Re: Date Dimension Aggr(max)) Repeating

cbaqir — Mon, 17 Sep 2018 15:04:20 GMT

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

Re: Date Dimension Aggr(max)) Repeating

stigchel — Mon, 17 Sep 2018 15:10:56 GMT

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

Re: Date Dimension Aggr(max)) Repeating

YoussefBelloum — Mon, 17 Sep 2018 15:20:47 GMT

Hi,

I think this calculated dimension will help get the expected output

=Month(DEFECT_STATUS_START_DATE)&Year(DEFECT_STATUS_START_DATE)

Re: Date Dimension Aggr(max)) Repeating

stigchel — Mon, 17 Sep 2018 15:29:59 GMT

Dimension

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