topic Re: Use LastWorkDate in Set Analysis in QlikView

Use LastWorkDate in Set Analysis

Anonymous — Tue, 22 Jul 2014 20:43:10 GMT

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

Re: Use LastWorkDate in Set Analysis

Anonymous — Tue, 22 Jul 2014 20:49:22 GMT

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

Re: Use LastWorkDate in Set Analysis

MK_QSL — Tue, 22 Jul 2014 20:49:25 GMT

can you provide some sample data ?

Re: Use LastWorkDate in Set Analysis

Anonymous — Tue, 22 Jul 2014 21:10:26 GMT

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

Re: Use LastWorkDate in Set Analysis

MK_QSL — Tue, 22 Jul 2014 21:18:59 GMT

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

Re: Use LastWorkDate in Set Analysis

MK_QSL — Tue, 22 Jul 2014 21:23:14 GMT

You just need to change your Variable Expression as below

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

Re: Use LastWorkDate in Set Analysis

Anonymous — Wed, 23 Jul 2014 14:58:33 GMT

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.

Re: Use LastWorkDate in Set Analysis

MK_QSL — Thu, 24 Jul 2014 07:57:47 GMT

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

Re: Use LastWorkDate in Set Analysis

Anonymous — Thu, 24 Jul 2014 08:36:07 GMT

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?

Re: Use LastWorkDate in Set Analysis

MK_QSL — Thu, 24 Jul 2014 08:39:11 GMT

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

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