Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

t_chetirbok
Valued Contributor

Comparison sum sales in Year with Previous Year

Hi!

I need to create straight table where Dimensions are Customer, Product, Storage, Year

and Expressions are sum(Sales) in Year and sum(Sales) in Previous Year.

Something like this:

where number in column F is Sales in 2015 and number in column G is Sales in 2015 -1

Any idea how can I do it?

Many thanks!

1 Solution

Accepted Solutions

Re: Comparison sum sales in Year with Previous Year

If you are ever going to select one year, then may be this:

=Before(Sum({<Year = {$(=If(GetSelectedCount(Year) = 1, GetFieldSelections(Year)-1, Chr(39) & '*' & Chr(39)))}>}Sales))

Capture.PNG

13 Replies

Re: Comparison sum sales in Year with Previous Year

There are two ways to do this:

1) Either use the As-of table in the script (The As-Of Table)

2) or use Above(TOTAL ...) method.

For a more precise help, can you provide a sample?

Re: Comparison sum sales in Year with Previous Year

That looks like a pivot table to me. If you want to do this in a straight table you'll have to create two expressions for each year that you want to show.


talk is cheap, supply exceeds demand
t_chetirbok
Valued Contributor

Re: Comparison sum sales in Year with Previous Year

Ok, it is a pivot table.

what I need to write in expression?

I have year in dimension and one expression is sum(sales) and second?

Re: Comparison sum sales in Year with Previous Year

Try sum(sales) and before(sum(sales))


talk is cheap, supply exceeds demand
t_chetirbok
Valued Contributor

Re: Comparison sum sales in Year with Previous Year

I attached small example

t_chetirbok
Valued Contributor

Re: Comparison sum sales in Year with Previous Year

I attached small example

Re: Comparison sum sales in Year with Previous Year

Gysbert's solution seems to be working, isn't it? or are you looking for something else?

Capture.PNG

t_chetirbok
Valued Contributor

Re: Comparison sum sales in Year with Previous Year

It is correct when Year doesn't select

and if select Year, for example 2016, it's not correct

Re: Comparison sum sales in Year with Previous Year

See attached example


talk is cheap, supply exceeds demand
Community Browser