topic Variance (Expenses, Income) in QlikView

Variance (Expenses, Income)

nevilledhamsiri — Wed, 25 Nov 2020 16:16:04 GMT

Please suggest me a set expression for variance calculation as per the data highlighted below. 1st data refers to variance of expenses between Months whereas 2nd one refers to variance of income between two Years!

Regds

Neville

EXPENSES
MONTH	ACT	BUDGET	VARIANCE (ACT Vs BUDGET)
JAN	5000	3000	2000
FEB	4000	5000	-1000
MAR	3000	3000	0

INCOME
MONTH	ACT-CY	ACY-LY	VARIANCE (CY Vs LY)
JAN	7500	10000	-2500
FEB	10000	11000	-1000
MAR	5000	4000	1000

Re: Variance (Expenses, Income)

felipedl — Mon, 02 Oct 2017 20:26:05 GMT

What is you want to do specifically Neville? Could you explain a little more?

Compare two years variance with current year?

Felipe.

Re: Variance (Expenses, Income)

nevilledhamsiri — Tue, 03 Oct 2017 00:21:43 GMT

Dear Felip,

1st the difference between ACT to Budget, 2nd , CY vs Ly after that the difference created against the LY in both cases separately.

Rgds

Neville

Re: Variance (Expenses, Income)

prma7799 — Wed, 04 Oct 2017 11:31:21 GMT

for Expense

1st Expression (ACT)

(SUM({$<FinancialYear=,MonthName=,Quarter=,FiscalYear={$(=max(FiscalYear))}

,[Date]={">=$(=MonthStart(max({$<FiscalYear={$(=max(FiscalYear))}>}[Date])))<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}[Date]))"}

>}[Actual])

2nd Expression(Budget)

(SUM({$<FinancialYear=,MonthName=,Quarter=,FiscalYear={$(=max(FiscalYear))}

,[Date]={">=$(=MonthStart(max({$<FiscalYear={$(=max(FiscalYear))}>}[Date])))<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}[Date]))"}

>}Budget)

)

3rd Expression (variance)

Column(1)-Column(2)