Variance - Againist previous Fiscal Year

[helen_pip]

Hello

I have a dataset dating back to 01/04/2012 and therefore I have 2 fiscal years worth of data, as my fiscal year runs from Apr-Mar

I have a list box which shows Month Number 1 , 2, 3, 4 etc

I then have 2 text boxes which show a count

Current Year: ='Current FY : ' &sum({<YearStart= {$(=Max(YearStart))

Last Year: ='Last FY : ' &sum({<YearStart = {$(=mAX(YearStart)-1)

However when the user selects Month Number 1 from the list box, the 2 text boxes work perfectly.  However if the user was to select month 3 and we are only in May.....There is no data for Month 3 so far, so my Current Year starts to show the data for last year as the text box is showing data for that month for the maximum year (Which will be last year until June 2013 data gets loaded in)

How do I get the Current Year to show 0 or N/A until we actually reach that time period in this financial year, and at that point, the report will work perfectly

Kind Regards

Helen

[vikasmahajan]

Please try following expressions

SUM({$<FiscalYear={$(=max(FiscalYear))}>}[Sales Amount])

SUM({$<FiscalYear={$(=max(FiscalYear)-1)}>}[Sales Amount])

Vikas

[helen_pip]

Hello

Thank you for your reponse

I incoporated set analysis with an if statement

='Current FY : ' & If(sum({<YearStart = {$(=mAX(YearStart)-1)}>}REFERRAL_TOTAL)=0,  If(sum({<YearStart = {$(=mAX(YearStart)1)}>}REFERRAL_TOTAL),sum({<YearStart={$(=Max(YearStart))}>}REFERRAL_TOTAL)

='Last FY : ' &  If(sum({<YearStart = {$(=MAX(YearStart)-1)}>}REFERRAL_TOTAL)<=0,sum({<YearStart= {$(=Max(YearStart))}>}REFERRAL_TOTAL),sum({<YearStart= {$(=Max(YearStart)-1)}>}REFERRAL_TOTAL)
)

This now works for me

Thanks for your help

[vikasmahajan]

Hii

Why u need if statement in this set  if you want to avoid null records  in Dimensions -> Tick Suppress When Value is Null.

Best of Luck.

Vikas