Use LastWorkDate in Set Analysis

[Anonymous]

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

[Anonymous]

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]

can you provide some sample data ?

[Anonymous]

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]

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 · Accepted solution]

You just need to change your Variable Expression as below

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

[Anonymous]

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]

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

[Anonymous]

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]

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