Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
mslottje
Creator II
Creator II

Date and time fucntions

Goodmorning experts,

I have two questions regarding a date and a time function.

  • Date:

I have a visualisation which shows the number of entered orders.

In the title of this visualisation, I want to show the name of the day and the date for yesterday.

I'm using the following expression:

='Orders entered '

&

if(num(WeekDay(Today()))= 6,(WeekDay(today())-2),(WeekDay(today())-1))

&' '&

if(num(WeekDay(Today()))= 6,(date(today())-2),(date(today())-1))

The expression shows me the numeric WeekDay and the numeric date of yesterday, where this should be the name of the day and the date in 'DD-MM-YYYY'.

Funny thing is, that this expression works as I want for today(), but not for (today()-1).

Any idea how to solve this problem?

  • Time:

To improve the data in this chart, I want to show the data of yesterday if the time of opening the sheet is before 16:00:00 ('h:mm:ss').

Otherwise, the chart should show the data of today. So, I'm searching for a proper expression for the following if statement:

if( "date of opening" < 16:00:00, date of yesterday, date of today)


Any idea how to write such an expression?


Hope you guys could help me out!


Cheers,

Mike

1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

Maybe with something like: date(today()-if(hour(now(2))<16,1,0), 'Format')

- Marcus

View solution in original post

10 Replies
ali_hijazi
Partner
Partner

may you please share a sample file so that we can help rapidly

I can walk on water when it freezes
marcus_sommer
MVP & Luminary
MVP & Luminary

You don't do today()-1 else weekday(today())-1 and therefore you get a numeric result. By weekday(today()-1) you should get the string-representation of the result. Beside this you could just format a date like you need it - ry it with something like:

= date(today()-(-alt(weekday(today())=6,0)+1), 'WWWW DD.MM.YYYY')

- Marcus

mslottje
Creator II
Creator II
Author

Hi Ali,

I've uploaded an example qvf file to my first message in this discussion!

mslottje
Creator II
Creator II
Author

Hi Marcus,

your first sentence already solved my first issue, thanks a lot!

Do you also know how to fix this with a time component and a now() expression?

marcus_sommer
MVP & Luminary
MVP & Luminary

What do you mean with "... time component and a now() expression"?

mslottje
Creator II
Creator II
Author

I want to show the date of yesterday if the time of opening was before 16:00:00.

If the time of opening was after 16:00:00, I want to show the date of today.


FYI, I've uploaded a sample qvf file to my first message in this discussion.

marcus_sommer
MVP & Luminary
MVP & Luminary

Maybe with something like: date(today()-if(hour(now(2))<16,1,0), 'Format')

- Marcus

mslottje
Creator II
Creator II
Author

Thanks Marcus, you helped me a lot!:)

ali_hijazi
Partner
Partner

='Orders entered '&

if(num(WeekDay(Today()))= 6,(WeekDay(date(num(today())-2))),(WeekDay(date(num(today()))-1)))

&' '&

if(num(WeekDay(Today()))= 6,(date(num(today())-2)),(date(num(today())-1)))

this is how it should be
be aware that date() expects a number while today() is a timestamp
weekday expects a date

I can walk on water when it freezes