Slicing Time Dimension - Correct Format

[george55]

Hi all,

When want to use an expression to slice a date/timestamp can I do it always with this format: '13/02/2019' ? eg.:

Sum( {<Date = {">'13/02/2019'"} >} Field)

Or do I have to take a look how my data looks, eg.: '2019-02-13 13:50:44.676000', so do I have to change it to?:

Sum( {<Date = {">'2019-02-13'"} >} Field)

[dplr-rn]

if you column is tagged as date/timstamp

you could do this 

Sum( {<Date = {">$(Date#('13/02/2019','MM/DD/YYYY'))"} >} Field)

[nikitadeshwal]

Hi,

If your date formate is date-timestamp eg. Date - '2019-02-13 13:50:44.676000'

first, convert it into date formate using 

date(Date) as Date1;

then

you can use this

sum({<Date1={">=$(='13/02/2019')"}>}Field)

Thanks,

Nikita Deshwal

[george55]

Thank you Nikitadeshwal and Dilipranjith for your posting. I'm now a little bit confused. Maybe I was unclear.

My question was if I can use the format '13/02/2019' anytime when the data is tagged as date/timestamp... even it looks different eg.: '2019-02-13 13:50:44.676000'?

in other words: when '13/02/2019'  is the 'internal' Qlik-engine format, so it is indepent of language regions and formats. If this is the case, in my logik this should to it: 

Sum( {<Date = {">'2019-02-13'"} >} Field)
so it is not necessary to use: Sum( {<Date = {">$(Date#('13/02/2019','MM/DD/YYYY'))"} >} Field)

Is my assumption right?

[tresesco]

I would say, your understanding is correct with a little correction in implementation. Try date value without additional single quotes, like:

Sum( {<Date = {">2019-02-13"} >} Field)

[george55]

Sorry, typo. I mean to use:

Sum( {<Date = {'>'13/02/2019'} >} Field)   
this should work always, it doesn't matter how your tagged timestamp/date looks like. May here a additional question. What is first, month or day? '13/02.. or '02/13/ ?

instead of using:
Sum( {<Date = {'>'2019-02-13'} >} Field)

correct?

[nikitadeshwal]

George,

See, when you use date() function to separate date from date-time stamp , the date will automatically appear in default qlik formate. 

this Formate, you can find it in the Main tab of  load script (  SET DateFormat='DD/MM/YYYY';  )

So because of date appear in this formate you cant not use this,   Sum( {<Date = {'>'2019-02-13'} >} Field)

and can use this,  sum({<Date1={">=$(='13/02/2019')"}>}Field)

hope you understood!

Thanks

[george55]

that means, Qlik is controlled by the variable DateFormat (in my case it is: SET DateFormat='DD.MM.YYYY';)? If yes, then I understood and know how to use it in future.

[tresesco]

Qlik maintains (default) the ISO date format : YYYY-MM-DD

[george55]

that means (sorry for writing again), I can use both formats:

'DD.MM.YYYY'  as in my Script the variable DateFormat is set

and

'YYYY-MM-DD'   because is the Qlik internal format?