Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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
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
Hi,
Can you attach the sample file that you are working.
Regards,
Jagan.