Counting previous year leads/appointments

[joshw]

I have created a page that reports data on our sales people. 

 

The area of the page I'm working on currently, has to do with leads and appointments from the current year and previous year. I have both of these areas working, but I want the previous year to also report up to today. 

 

For example:

 

Jan-Dec 2012 Leads    Appts

                        400        80

Jan-Dec 2011  
                          300      65

But if today is December 10th, I want it to read last years up til today.

Jan-December 2012 Leads     Appts
                                   400          80
Jan-December 10th    275          50

I am using a relationship and a calculated field to find the data for the Current and Previous year, but I am not sure how to make it look for the previous year up til today. This obviously would only apply to the Previous year leads/appts and Previous year current month leads/appts.

I'm not sure what information you'd need if you need to know anything, but I can describe in more detail if need be. Thanks.     

[comment]

You could do a find in the form of =

 

Let ( [
today = Get ( CurrentDate ) ;
y = Year ( today ) - 1
] ;
Date ( 1 ; 1 ; y ) & ".." & Date ( Month ( today ) ; Day ( today ) ; y )
)

[joshw]

Would I put that for the field itself or with the filter for the portal? 

[comment]

Neither. It would go into a script that finds the records in their own table. If you prefer to use a relationship, define two calculation fields in the parent table:

 

cPrevYearStart =

Date ( 1 ; 1 ; Year ( Get ( CurrentDate ) ) - 1 )

 

cPrevYearEnd =

Let ( [
today = Get ( CurrentDate )
] ;
Date ( Month ( today ) ; Day ( today ) ; Year ( today ) - 1 )
)

 

then use these to define a new relationship to the child table, say:

 

Salespeople::SalespeopleID = Appointments 2::SalespeopleID

AND

Salespeople::cPrevYearStart ≤ Appointments 2::Date

AND

Salespeople::cPrevYearEnd ≥ Appointments 2::Date

 

---

Note that both calculation fields need to be unstored and the result type should be Date.

 

 

If the number of related records is not too great, you could filter the existing portal instead, using the following filtering condition:

Let ( [
today = Get ( CurrentDate ) ;
y = Year ( today ) - 1
] ;
Date ( 1 ; 1 ; y ) ≤ Appointments::Date
and
Appointments::Date ≤ Date ( Month ( today ) ; Day ( today ) ; y )
)

[joshw]

Brilliant, this worked great. Thanks!

[joshw]

Let ( [

today = Get ( CurrentDate ) ;
y = Year ( today ) - 1
] ;
Date ( 1 ; 1 ; y ) ≤ Pool Leads 5::Lead Date
and
Pool Leads 5::Lead Date ≤ Date ( Month ( today ) ; Day ( today ) ; y )
)
and
Pool Leads 5::Lead Type = "New Pool Lead"

 

This is what I ended up using, and now that we've begun the new year, it can't seem to look back at the previous january. Should I use a Case() statement? I attempted to do that (If it's January...etc.) and that seemed to not work because there was to many perimeters. What am I missing/doing wrong?

[comment]

I think you need to restate your purpose. The way you have it now, the portal will show records from previous year up to the date in previous year corresponding to the current date in this year. This means that if today is January 2, 2013 then the portal will show only records from the first two days of 2012. I thought that was exactly what you asked for.

[joshw]

I think you need to restate your purpose. The way you have it now, the portal will show records from previous year up to the date in previous year corresponding to the current date in this year. This means that if today is January 2, 2013 then the portal will show only records from the first two days of 2012. I thought that was exactly what you asked for.

That purpose hasn't changed. I can look in the records and see appointments on Jan 1 and Jan 2 of last year. I realized I posted the Lead text here, but this is the text I'm having issues with on the same table, but just different field:

 

 

Let ( [

today = Get ( CurrentDate ) ;

y = Year ( today ) - 1

] ;

Date ( 1 ; 1 ; y ) ≤ Pool Leads 5::Appointment Date

and

Pool Leads 5::Appointment Date ≤ Date ( Month ( today ) ; Day ( today ) ; y )

)

[comment]

I am afraid I still don't follow. The filter you show, same as the one you posted earlier, will show records falling in January 1 and January 2 of previous year (assuming today is January 2). Is that not what you want?

[joshw]

It IS what I want, but it is NOT showing that. It's currently showing nothing, which is incorrect because I can go to that table and look up the information it should be showing and find it.

[comment]

Just to make sue: this is a portal filter, right? Are you sure the portal is set to show records from Pool Leads 5?

[joshw]

Yes, certain of this. Just double checked. 

[comment]

I don't know. Does the attached work for you?

FilterYTD.fp7.zip