topic Fiscal year issue in QlikView

Fiscal year issue

Mon, 10 Oct 2011 18:20:28 GMT

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,

Fiscal year issue

swuehl — Mon, 10 Oct 2011 18:48:59 GMT

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

Fiscal year issue

Tue, 11 Oct 2011 01:38:35 GMT

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.

Re: Fiscal year issue

swuehl — Tue, 11 Oct 2011 09:28:20 GMT

Hi,

what problems do you had with using group by?

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

Regards,

Stefan

Fiscal year issue

Fri, 14 Oct 2011 11:21:34 GMT

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

regards,