Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
What is the expression for the sum of sales for the 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.
I assume you have something like an OrderDate:
=sum({<OrderDate = {">=$(=Yearstart(today()))"}>} SalesAmount)
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
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
Thanks a lot, I'll have a look through
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?
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.
Yeah, I did a pivot table, much easier way to go about it.
Thanks for your help.