Calculated Dimension Date Format

[ZoeM]

Hi!

I have a calculated dimension (shown below) where I want to show it as a date format, but the date format is not what/how I have asked Qlik to produce.

=Date#( Aggr( Concat( {$<[Date Type]={'Current Dates'},

Gateways={'Design'}>} DISTINCT Date ) ,[MY Program]),'DD-MMM-YY')

The result is MMM,D,YYY when I want DD-MMM-YY. I changed the Document Properties default but that still didn't work.

I tried removing the '#' and using Date() but that did not work either. I tried combining the two and still did not work. 

Any ideas?

[vchuprina]

Hi,

Try this:

=Date(Date#( Aggr( Concat( {$<[Date Type]={'Current Dates'},

Gateways={'Design'}>} DISTINCT Date ) ,[MY Program]), 'MMM,D,YYY') ,'DD-MMM-YY')

Date#() function helps Qlik to recognize Date values, Date() transforms value to the necessary format

Regards,

Vitalii

[ZoeM]

Thanks for the response.

I tried it and it is not giving me any results.

[vchuprina]

Could you please share result of this expression?

Aggr( Concat( {$<[Date Type]={'Current Dates'}, Gateways={'Design'}>} DISTINCT Date ) ,[MY Program])

[vchuprina · Accepted solution]

Hi, 

Try this, first change format, then concatenate

Aggr(
Concat( {$<[Date Type]={'Current Dates'}, Gateways={'Design'}>} DISTINCT DATE(Date#(Date, 'MMM,DD,YYY'), 'DD-MMMM-YY'), ',')
,[MY Program])

Regards,

Vitalii

[ZoeM]

Hi Vitalli.

Unfortunately I do not have dummy data that I can share.

I tried the above equation and it did not give me any results. 

[vchuprina]

Last expression works fine in my app. 
Check date format of Date column in your table. And update second parameter in date#() function in your expression  .

Also add [MY account] as a dimension to your chart

[ZoeM]

Please see snip below.

I have the expression above as a Calculated Dimension with Date Header and as you can see below it is blank. 

When you say to check the format of the Date column in the table, since its a calculated dimension I dont know how to check its format outside of the expression parameters...

[ZoeM]

Hi Vitalli

I have to apologize. I went into the document properties and changed the default date formatting and I didn't realize I also needed to restart the application for the change to take effect.

I am all set now, thanks for all your hard work and great help!