Set Analysis for Dynamic Past Two Year Total

[#1]

Hello,

I try to get dynamic sum of sale for previous two years. For example, when I select 2011, I would like to see the total sum of sale for Year 2010 and 2009, and when I select 2012, I would like to see the total sum of sale for Year 2011 and 2010. In the set analysis, which expression am I supposed to put for the field "Sale Year"?

Thank you so much!

[Gysbert_Wassenaar]

Try: sum({<[Sale Year]={'>=$(=only(Sale Year])-2)<$(=only([Sale Year]))'}>} sale_amount)

Note: set analysis calculates one set for the entire chart, not a set per row. That means you can't use Sale Year as dimension. You have to select exactly one year.

[MayilVahanan]

HI

Try this

sum({<[Sale Year]={'>=$(=max(Sale Year])-2)<$(=max([Sale Year]))'}>} sale_amount)

Hope it helps

[nizamsha]

=sum({$<year={"*"},year-={"$(=Max(year))"}>}sale_amount)

[#5]

So, what if I potentially have other date dimension, such as month or monthname. If I don't want the sum value to change even though I select a specific month/month name variables, should I use the following expression:

sum({<[Sale Year]={'>=$(=only(Sale Year])-2)<$(=only([Sale Year]))', [Sale Month]=, [Sale Month Name]=, [ Sale Date]=}>}?

Thanks!

[#6]

So, what if I potentially have other date dimension, such as month or monthname. If I don't want the sum value to change even though I select a specific month/month name variables, should I use the following expression:

sum({<[Sale Year]={'>=$(=only(Sale Year])-2)<$(=only([Sale Year]))', [Sale Month]=, [Sale Month Name]=, [ Sale Date]=}>}?

Thanks!

[#7]

So, what if I potentially have other date dimension, such as month or monthname. If I don't want the sum value to change even though I select a specific month/month name variables, should I use the following expression:

sum({<[Sale Year]={'>=$(=only(Sale Year])-2)<$(=only([Sale Year]))', [Sale Month]=, [Sale Month Name]=, [ Sale Date]=}>}?

My dimension in Chart is actually mm/yyyy, and I want ALL the months of a particular year show up the same total sale value for previous two year. Do you know what would be a good way to do it?

Thanks a lot!

[#8]

So, what if I potentially have other date dimension, such as month or monthname. If I don't want the sum value to change even though I select a specific month/month name variables, should I use the following expression:

sum({<[Sale Year]={'>=$(=only(Sale Year])-2)<$(=only([Sale Year]))', [Sale Month]=, [Sale Month Name]=, [ Sale Date]=}>}?

My dimension in Chart is actually mm/yyyy, and I want ALL the months of a particular year show up the same total sale value for previous two year. Do you know what would be a good way to do it?

Thanks a lot!

[#9]

I have a similar issue, where I have a dimension called Start Week, which is the first day of the week in YYYY-MM-DD.   I want to calculate based on each rows "Start Week", the previous weeks sales.  So that I can do a WoW delta.  I don't want to use the "Above" function because this would force the user to show two rows of data. 

For example, if they selected one single start week selection, "2014-09-05"  then only one row should be displayed