# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Skip the ticket, Chat with Qlik Support instead for instant assistance.
cancel
Showing results 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:

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
MVP

You just need to change your Variable Expression as below

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

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

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

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])

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.

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?

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...

Community Browser