[nschaaf Contributions: 0Level 1☆]

I’m running a project search that pulls the invoices in each project. The reason I am not doing a transaction search is because I am grouping by a field on the project (Project Type – which is really our companies different departments). When I tried to group a transaction search by project type (company department) it didnt work.

The saved search pulls invoices for 2014 and 2015. My results criteria are project type (grouped) and amount (summed).

I.e.

Tax xxx,xxx

Assurance xxx,xxx

Outsource xxx,xxx

Consulting xxx,xxx

Total xxx,xxx

I want to be able to categorize the numbers as:

1. Month to date for 2015 and 2014. I.e. Revenue for 7/1/2015 – today, and then 7/1/2014 – today in 2014. This would change each month, so in August it would start pulling numbers from 8/1 going forward for the month.

2. And year to date for 2015 and 2014. 2015 this is just the current total i.e. 1/1/2015 – today and for 2014 this is would be 1/1/2014 – today in 2014.

I have a transaction search for revenue where I’ve created the year to date formulas for # 2 but I dont know how they would translate to this project search. Those formulas are listed below:

This gives me year total for 2015 and 2014 (just change the year) – Decode(To_char({trandate},’YYYY’),’2015′, {amount},”)

This gives me the year to date for 2014 (from 1/1/2014 to today in 2014) – CASE WHEN {trandate} BETWEEN to_date(‘1/1/2014’, ‘MM/DD/YYYY’) AND to_date(to_char({today},’YYYY’)-1||to_char({today},’MMDD’),’YYYYMMDD’) THEN {amount} ELSE 0 END

Thanks for any help or insight you guys might have. Please let me know if I can be more descriptive or clear anything up.
This is a cached copy. Click here to see the original post.

[Author]

Posts