topic Re: Variance by year on expression in QlikView

Variance by year on expression

Thu, 05 Apr 2012 11:42:55 GMT

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

	2011	2010	Var €	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

Many thanks in advance.

Lionel

Re: Variance by year on expression

Thu, 05 Apr 2012 12:17:02 GMT

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

jagan — Thu, 05 Apr 2012 12:43:06 GMT

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.

Re: Variance by year on expression

Thu, 05 Apr 2012 13:37:16 GMT

Hi,

Manu thanks for this prompt reply but I am still facing an issue

If I place the 2 dimensions on the pivot table I obtain something like this

Ca stand for sales and marge brute for gross margin

Here is my set up

Dimension

Expression

I must have made something wrong

De : jagan mohan

Envoyé : jeudi 5 avril 2012 14:43

À : lionel925

Objet : - Re: Variance by year on expression

<http://community.qlik.com/index.jspa> QlikCommunity

Re: Variance by year on expression

created by jagan mohan <http://community.qlik.com/people/jagan> in Development (QlikView Desktop) - View the full discussion <http://community.qlik.com/message/207757#207757

Variance by year on expression

jagan — Thu, 05 Apr 2012 13:50:24 GMT

Hi,

Can you attach the sample file that you are working.

Regards,

Jagan.