topic Re: Calculated Dimension Date Format in QlikView

Calculated Dimension Date Format

ZoeM — Tue, 17 May 2022 13:03:03 GMT

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?

Re: Calculated Dimension Date Format

vchuprina — Tue, 17 May 2022 13:27:26 GMT

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

Re: Calculated Dimension Date Format

ZoeM — Tue, 17 May 2022 14:01:27 GMT

Thanks for the response.

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

Re: Calculated Dimension Date Format

vchuprina — Tue, 17 May 2022 14:03:03 GMT

Could you please share result of this expression?

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

Re: Calculated Dimension Date Format

vchuprina — Tue, 17 May 2022 14:15:40 GMT

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

Re: Calculated Dimension Date Format

ZoeM — Tue, 17 May 2022 14:46:24 GMT

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.

Re: Calculated Dimension Date Format

vchuprina — Tue, 17 May 2022 15:39:53 GMT

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

Re: Calculated Dimension Date Format

ZoeM — Tue, 17 May 2022 16:48:06 GMT

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

Re: Calculated Dimension Date Format

ZoeM — Tue, 17 May 2022 18:07:17 GMT

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!