Max of date created -1 variable

[shonarach]

Hi I am trying to create a variable in my data.

We are running a database that is a day behind so on Friday there is data populated but this is not seen on Monday.

At the moment my code is

Count({<[ActualDate Created] ={'$(=Max([ActualDate Created]))'}>}[CountryRefNumber])

This is giving me the count of yesterday's data

If it is a Monday it is giving me Sundays Data

What I want to do is, if it is Monday then count all the data from Friday to Sunday. I have tried so far

Count({<[ActualDate Created] ={'$(=Max([ActualDate Created]))'}>}[CountryRefNumber]) +Count({<[ActualDate Created] ={'$(=Max([ActualDate Created]-1))'}>}[CountryRefNumber]) + Count({<[ActualDate Created] ={'$(=Max([ActualDate Created]-2))'}>}[CountryRefNumber])

But this is not giving me the correct data.

Max(Date([ActualDate Created])) = 13/05/2016

Max(Date([ActualDate Created])) - 1 = 42502

Any help is appreciated.

[swuehl]

Try to use FirstWorkDate() to get the last working date (I assume that's what you want):

=FirstWorkDate(today()-1,1)

resp:

FirstworkDate(Max([ActualDate Created]),1)

Count({<[ActualDate Created] ={'$(=FirstWorkDate(Max([ActualDate Created]),1))'}>}[CountryRefNumber])

[#3]

Max(Date([ActualDate Created] )) - 1 is converted to number, use

Date(Max(Date([ActualDate Created])) - 1)

[shonarach]

Thank you very much for the reply!

The FirstworkDate(Max([ActualDate Created]),1)  is great for giving me the first Monday in the working week.

Do you think it would be possible to use this for an If statement?

Could I tell Qlikview:

If (today () = Firstworkday, then count all logged data from Friday to Monday, Count last day's logged Data)

Thanks

[shonarach]

The code is working great

Perhaps you could point me in the right direction with my if statement?

Could I tell Qlikview:

If (today () = Firstworkday, then count all logged data from Friday to Monday, Count last day's logged Data)

Really appreciate the quick reply

[swuehl · Accepted solution]

Despite its name, FirstWorkDate() is not giving you the first Monday in the working week.

It's returning the (Working) Date you would need to start to end 1 working day work.

Hence, if Max(ActualDateCreated) is today, it should return today. If it's a weekend,it should return friday.

I've understood that's what you were asking for (in combination with subtracting 1 from the Max())

[shonarach]

Yes you have answered my query about the max date - 1, thank you very much