Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
shonarach
Contributor III
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
swuehl
MVP
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())

View solution in original post

6 Replies
swuehl
MVP
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)

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

shonarach
Contributor III
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)

Really appreciate the quick reply

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

shonarach
Contributor III
Contributor III
Author

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