Data in where clause smaller than certain other date

[Anonymous]

Hello,

I'm having the following script:

LastContactDate:
LOAD FirstSortedValue(ActivityDate,-ActivityDate) as LastContactUntilEnd2016,
[Account Name]
Resident ActivityData
Where Date(ActivityDate) < Date(makedate(2017,1,1))
Group by [Account Name];

In the where clause, I want to make sure that I only include rows in the load that are smaller than Jan 1st 2017.

Although there is data in 2016, it shows me nothing. When I remove the where clause, it just gives me the last contact date so it looks to me that the rest of the script should be fine.

Any idea on how to approach this?

Kind regards,

Christophe

[Kushal_Chawda]

Please check ActivityDate is in proper date format. Take a list box and check whether it is left aligned, if so it should be in proper date format.

Where floor(ActivityDate) < floor(makedate(2017,1,1))

[Anil_Babu_Samineni]

What is your intention with FirstSortedValue() over here. Can you try this? and show the snap

LastContactDate:
LOAD ActivityDate,
[Account Name]
Resident ActivityData
Where Date(ActivityDate) < Date(makedate(2017,1,1))
Group by [Account Name];

PS - Here, how is your date format looks like. could be the reason with date format. And MakeDate(2017, 1, 1) will return as 01/01/2017. Does this same format with ActivityData

[Anonymous]

Hi Kushal,

They are right aligned (just like my numbers). Are you sure left alignment means correct date? When I change the formatting to "number", it properly changes it to a number so I assume the formatting is fine.

The floor option did not yield any results as well.

Kind regards,

Christophe

[Anonymous]

Hi Anil,

The purpose of the firstsortedvalue (and maybe there's the reason why it's hicking up) is because I want to show the last activity date before Jan 1st 2017.

My table contains a set of values which ranges from 2014 till today. I want to select the last date prior to 2017.

Could it be that the firstsortedvalue happens first and ONLY THEN the where clause kicks in?

Eg: If the latest contact date was in 2017, it selects that data, and then it filters to exclude everything

If I change this line:

FirstSortedValue(ActivityDate,-ActivityDate) as LastContactUntilEnd2016,

to

FirstSortedValue(ActivityDate,ActivityDate) as LastContactUntilEnd2016,

(= changing the sorting order), there is a value showing.

Any thoughts?

Kind regards,

Christophe

[Anil_Babu_Samineni]

Good, Can you please share 10 rows of ActivityDate and AccountName details, So that we can try in qvw

[Anonymous]

Sure! Here you go

ActivityDate	Account Name
5/05/17	Christophe
24/04/17	Christophe
4/01/17	Christophe
8/11/16	Christophe
3/11/16	Christophe
7/09/16	Christophe
31/08/16	Christophe
25/08/16	Christophe
9/08/16	Christophe
20/07/16	Christophe

[Kushal_Chawda]

sorry I confused you, right aligned means correct date format, left aligned are text.

[Kushal_Chawda]

or try this

Where date(ActivityDate,'DD/MM/YYYY') < date#('01/01/2017','DD/MM/YYYY')

Can you share sample data or app?

[Anil_Babu_Samineni]

Seems, This is working

LOAD Date(Date#(ActivityDate,'DD/MM/YY'),'DD-MM-YYYY') as ActivityDate, [Account Name] where Date(Date#(ActivityDate,'DD/MM/YY'),'DD-MM-YYYY') >= '01-01-2017';

LOAD * Inline [

ActivityDate, Account Name

5/05/17, Christophe

24/04/17, Christophe

4/01/17, Christophe

8/11/16, Christophe

3/11/16, Christophe

7/09/16, Christophe

31/08/16, Christophe

25/08/16, Christophe

9/08/16, Christophe

20/07/16, Christophe

];