topic Re: Max of date created -1 variable in QlikView

Max of date created -1 variable

shonarach — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Max of date created -1 variable

swuehl — Tue, 17 May 2016 15:33:47 GMT

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

Re: Max of date created -1 variable

Tue, 17 May 2016 21:16:19 GMT

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

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

Re: Max of date created -1 variable

shonarach — Wed, 18 May 2016 13:16:15 GMT

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

Re: Max of date created -1 variable

shonarach — Wed, 18 May 2016 13:18:20 GMT

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

Re: Max of date created -1 variable

swuehl — Wed, 18 May 2016 15:38:34 GMT

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

Re: Max of date created -1 variable

shonarach — Wed, 18 May 2016 15:50:03 GMT

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