date format & timestamp format

[dreweezy]

I've got a date column that displays it as -- '2019-07-31 00:00:00.000000'

I'd like to do some manipulation in qlik and also have it very simple to show it in a format such as 'MM/DD/YYYY'

I've tried to do a few manipulation hoping to produce it in that format but ends up being null. 

Syntax that has been tried:

1. Date(Date#([marketingDate], 'MM/DD/YYYY') ) AS [Marketing Date]

2. Timestamp(Timestamp#([marketingDate], 'YYYY/MM/DD hh:mm:ss') ,'MM/DD/YYYY') AS [Marketing ]

Still ends up showing either wrong or empty.

[Frank_Hartmann]

try this:

date(date#(left([marketingDate],10),'YYYY-MM-DD'),'MM/DD/YYYY') AS [Marketing Date]

[dreweezy]

Does not work. Any other suggestions? Feel like this is something that should be a simple fix.

[dreweezy · Accepted solution]

Solution:

Date([YOURFIELDDATE], 'MM-DD-YYYY') as [DATE]

[dreweezy]

Hey Frank thanks for your help. Wondering if you can help me out with my syntax.

I am coloring cells depending on what different in days.

if(
[marketingDate] - [financeDate] < 30, green(),
if(([marketingDate] - [financeDate]) > 30 and < 90, lightred())

My syntax is wrong and wondering how to set up the in between factor when running an if statement in qlik.

Thanks.

[Frank_Hartmann · Accepted solution]

Maybe this:

if([marketingDate] - [financeDate] < 30, green(),
if(([marketingDate] - [financeDate]) > 30 and [marketingDate] - [financeDate]) < 90, lightred())

[dreweezy]

Thanks Frank. Works like a charm.