cancel
Showing results for
Did you mean:  Contributor III

## Max of date created -1 variable

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.

1 Solution

Accepted Solutions  MVP

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

6 Replies  MVP

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

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

Date(Max(Date([ActualDate Created])) - 1)  Contributor III
Author

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  Contributor III
Author

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

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())  Contributor III
Author

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