Fiscal year issue

[#1]

Hi,

I am trying to create a report based on FY(Oct to Sep). I need to sum all values with date between Oct 2010 - Sept 2011. Again, this report should be dynamic to take next FY as Oct 2011 - Sept 12.

I have a date field called CloseDate which is used to create ClosedDateMade and is correct.

OPP2:

Load

MakeDate(mid(ClosedDate,7,4),SubField(ClosedDate,'/',1),SubField(ClosedDate,'/',2)) as ClosedDateMade,

YearName ( MakeDate(mid(ClosedDate,7,4),SubField(ClosedDate,'/',1),SubField(ClosedDate,'/',2)), 0, 10 ) as FiscalYRNAME,// to implement FY OCT-SEP

Amount*Rate as AmtCon

resident OPPORTUNITYFIRST;

O1:

Load

Date(Today()) as TodayDate,

sum(if(FiscalYRNAME = YearName(today(),0,10),AmtCon))

as v1 Resident OPP2;

When I remove sum(), it executes correctly but implementing sum() just hangs the script without any error in log!

regards,

MR

[swuehl]

I believe you need a group by clause added to your second load to be able to use an aggregation function (i.e. sum),

maybe like

O1:

Load

Date(Today()) as TodayDate,

sum(if(FiscalYRNAME = YearName(today(),0,10),AmtCon)) as v1

resident OPP2 group by FiscalYRNAME;

I would probably use soemthing like

O1:

Load

FiscalYRNAME,

sum( AmtCon) as FYSum

resident OPP2 group by FiscalYRNAME;

to sum all Fiscal years, not only todays FY, giving a table with all FY and their sum.

Don't know why it hangs, it should give you an appropriate error message.

Regards,

Stefan

[#3]

Thanks for response Stefan!

I tried group by but no luck!

I was using following code and it is working fine without group by:

//O1:

//Load

//Date(Today()) as TodayDate,

//sum(if(FiscalYear= Year(today()),if(wildmatch(StageName,'7*')=1, AmtCon))) as v1 Resident OPP2;

But now, I need to calculate FY from ClosedDate field and I am using YearName for this.

MR

[swuehl · Accepted solution]

Hi,

what problems do you had with using group by?

Please see attached, it seems to work just fine for me.

Regards,

Stefan

[#5]

Thanks! I have used some other way to do this calculation. Your input was helpful!

regards,

MR