4 Replies Latest reply: Apr 5, 2012 9:50 AM by jagan mohan rao appala

# Variance by year on expression

Hello

First of all I am a beginner.

Let say that we have a table with 6 fields : Customer, customer familly, ,order number, year,  Sales, gross margin

The goal is to produced a straight table or a pivot table will the following information

20112010Var  €Var %
Sales

Gros margin

I have place year(date) as a dimnesion calculated in the load and Sales and gross margin as expression

I'am now wondering how to calculates variances ?

Must be simple but i have spend time on that without any solution

Lionel

• ###### Re: Variance by year on expression

Hello you can use this expression :

for N (when you select 2011) :

sum({\$<DateField={\$(=Only(DateField))}>} SalesField)

for N-1

sum({\$<DateField={\$(=Only(DateField)-1)}>} SalesField)

Var €

sum({\$<DateField={\$(=Only(DateField))}>} SalesField) - sum({\$<DateField={\$(=Only(DateField)-1)}>} SalesField)

Var %

(sum({\$<DateField={\$(=Only(DateField))}>} SalesField) / sum({\$<DateField={\$(=Only(DateField)-1)}>} SalesField)-1)*100

and the same for Gros margin

• ###### Variance by year on expression

Hi,

Use this Sales and Gross margin as Dimensions and use the following expressions

For 2011

=Sum({<Year={'2011'}>} Sales)

For 2010

=Sum({<Year={'2010'}>} Sales)

For Var \$

=Sum({<Year={'2011'}>} Sales) - Sum({<Year={'2010'}>} Sales)

For Var %

=Sum({<Year={'2011'}>} Sales) / Sum({<Year={'2010'}>} Sales)

Hope this helps you.

Regards,

Jagan.