Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

chrisgoudy
New Contributor II

Sum of Sales for most Recent year

What is the expression for the sum of sales for the most recent year?

1 Solution

Accepted Solutions
MVP
MVP

Sum of Sales for most Recent year

Let's get started with a literal:

=sum({<Year = {2008}>} Sales)

=sum({<Year = {2009}>} Sales)

...

This should be equivalent to

=sum( if(Year = 2008, Sales))

etc.

You can also just create a straight table chart or bar chart with dimension Year and expression

=sum(Sales)

which will automatically group your sales by year.

7 Replies
MVP
MVP

Sum of Sales for most Recent year

I assume you have something like an OrderDate:

=sum({<OrderDate = {">=$(=Yearstart(today()))"}>} SalesAmount)

chrisgoudy
New Contributor II

Sum of Sales for most Recent year

not exactly sure, I was thinking of using something more along the lines of

Sum(Sales,MaxYear)

But I'm not sure where to place MaxYear or how to write it in

MVP
MVP

Sum of Sales for most Recent year

So can I assume you have a Year field?

Then try

=sum({<Year = {'$(=max(Year))'}>} Sales)

or

=sum({<Year = {'$(=year(today()))'}>} Sales)

The above should be equivalent to

=sum(if (Year = year(today()), Sales))

used e.g. in a text box, but better performing.

I also found this series of blogs quite helpful, especially if you are starting with set analysis (like in my examples).

http://iqlik.wordpress.com/2010/11/27/the-magic-of-set-analysis-point-in-time-reporting/

And please also have a look into the Help pages, 'Set Analysis'.

Hope this helps,

Stefan

chrisgoudy
New Contributor II

Sum of Sales for most Recent year

Thanks a lot, I'll have a look through

chrisgoudy
New Contributor II

Sum of Sales for most Recent year

If I want to find the sum of sales for each year

eg

2008

2009   

2010

2011    

can I use the same expression you have above:

=sum({<Year = {'$(=max(Year))'}>} Sales)

and replace max(Year) with the start and end dates of each specific year?

MVP
MVP

Sum of Sales for most Recent year

Let's get started with a literal:

=sum({<Year = {2008}>} Sales)

=sum({<Year = {2009}>} Sales)

...

This should be equivalent to

=sum( if(Year = 2008, Sales))

etc.

You can also just create a straight table chart or bar chart with dimension Year and expression

=sum(Sales)

which will automatically group your sales by year.

chrisgoudy
New Contributor II

Sum of Sales for most Recent year

Yeah, I did a pivot table, much easier way to go about it.

Thanks for your help.