topic Variance - Againist previous Fiscal Year in QlikView

Variance - Againist previous Fiscal Year

helen_pip — Mon, 13 May 2013 12:42:18 GMT

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

Re: Variance - Againist previous Fiscal Year

vikasmahajan — Mon, 13 May 2013 12:53:18 GMT

Please try following expressions

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

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

Vikas

Re: Variance - Againist previous Fiscal Year

helen_pip — Mon, 13 May 2013 13:10:25 GMT

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

Re: Variance - Againist previous Fiscal Year

vikasmahajan — Tue, 14 May 2013 06:14:39 GMT

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