Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

Many thanks in advance.

Lionel

4 Replies
Not applicable
Author

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

jagan
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

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

jagan
Luminary Alumni
Luminary Alumni

Hi,

Can you attach the sample file that you are working.

Regards,

Jagan.