Sum of Sales for most Recent year

[Anonymous]

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

[swuehl]

I assume you have something like an OrderDate:

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

[Anonymous]

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

[swuehl]

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

[Anonymous]

Thanks a lot, I'll have a look through

[Anonymous]

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?

[swuehl · Accepted solution]

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.

[Anonymous]

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

Thanks for your help.