Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mslottje
Contributor 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

Re: Date and time fucntions

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

- Marcus

10 Replies
ali_hijazi
Honored Contributor

Re: Date and time fucntions

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

Re: Date and time fucntions

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
Contributor II

Re: Date and time fucntions

Hi Ali,

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

mslottje
Contributor II

Re: Date and time fucntions

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?

Re: Date and time fucntions

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

mslottje
Contributor II

Re: Date and time fucntions

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.

Re: Date and time fucntions

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

- Marcus

mslottje
Contributor II

Re: Date and time fucntions

Thanks Marcus, you helped me a lot!Smiley Happy

ali_hijazi
Honored Contributor

Re: Date and time fucntions

='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

Community Browser