Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Use LastWorkDate in Set Analysis

Hi!

I've going over this one over and over again, but now I give up and ask for your help 🙂

Using set analysis I have an expression in a chart, which gives me the result of yesterday (created that using inline load in my script and works great). Only problem is on Mondays, where I don't want to look at Sunday's sales, but instead Friday's sale.

I've been trying to alter this in the inline load without luck, in the set analysis, whihc obviously didn't work and finally trying to create a variable by defining last work date, but for some reason that didn't work either. Here's my last try on creating a variable and using that in my set analysis

sum({<[Appointment - Created Date]={'$(vLastWorkDay)'}>} [Appointment - Count])

Definition of variable:

Capture.PNG.png

Any suggestions on how to solve this one? I'm certain it's simple and can be done in several ways...

Thank you

Claus

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

You just need to change your Variable Expression as below

=IF(WeekDay(Max(Date))=0,Date(Max(Date)-3),Date(Max(Date)-1))

View solution in original post

9 Replies
Anonymous
Not applicable
Author

A quick add-on: Using the simple expression: LastWorkDate(Today()-1,1)

I get yesterdays workday, but can't use that expression in my set analysis and neither does it work as a variable

MK_QSL
MVP
MVP

can you provide some sample data ?

Anonymous
Not applicable
Author

Hi Manish

Thank you for your reply. I've tried to put together an example with dummy data. Hope it makes sense. I've also creaed the variable in there

MK_QSL
MVP
MVP

Use below expression in your Variable Definition

=IF(WeekDay(Max([Appointment - Created Date]))=0,Date(Max([Appointment - Created Date])-3),Date(Max([Appointment - Created Date])-1))

Now you can use your expression to get the SUM of AppointmentCount

=SUM({<[Appointment - Created Date] = {"$(=vLastWorkDay)"}>}[Appointment - Count])

MK_QSL
MVP
MVP

You just need to change your Variable Expression as below

=IF(WeekDay(Max(Date))=0,Date(Max(Date)-3),Date(Max(Date)-1))

Anonymous
Not applicable
Author

Hi Manish

Thank you for your help - Solution worked rigth away.

I had hoped to use the solution when looking ahead as well (same problem as when it's Friday and looking at tomorrow Saturday, where I actually want to see Monday). I have appointments (sales meetings) booked in the future and there I can't use the "max logic" on the date as they go far ahead.

Is there a way to link my Date field = Today()+1? I can't get my head around that one.

MK_QSL
MVP
MVP

=IF(WeekDay(Max(Date))=4,Date(Max(Date)+3),Date(Max(Date)+1))

Anonymous
Not applicable
Author

Hi Manish,

But as I can have dates in the future I don't assumen the Max function will work, when I want to look at tomorrow's data?

MK_QSL
MVP
MVP

=IF(WeekDay(Date)=4,Date(Date+3),Date(Date+1))

or

=IF(WeekDay(Max(Date))=4,Date(Max(Date)+3),Date(Max(Date)+1))


This will work if the selected date is not the MaxDate of your database, as you don't have data for the same...