Using TOTAL with set analysis by Year

[zagzebski]

I need to do the following. Get a total of ALL sales for a month but for only a specific year:

Data:

Client    Month   Sales   Year

A             Jan       100   2013

A             Feb        50   2013   

B             Jan        200  2013

B              Feb      100  2013

A             Jan        75   2012

A             Feb        50   2012   

B             Jan        100  2012

B              Feb       75   2012

Object results:

Client   Month     Sales-2103   Sales-2012

A          Jan         300               175

B          Jan         300               175

A          Feb         150               125

B          Feb         150               125

Basically have to add Set analysis for 2012 and for 2013 to this expression:Sum(total<Month>Sales)... but I must have my syntax wrong because I am coming back with blanks.

[jpapador]

Two expressions:

1. Sum({$<Year={2013}>}Sales

2. Sum({$<Year={2012}>}Sales

Make your dimensions Client and Month

[zagzebski]

This would not total for ALL sales regardless of the client in the dimension.

[jpapador]

Ah I'm sorry.  I think what you are looking for is:

Sum(TOTAL <Year, Month> Sales)

[Gysbert_Wassenaar · Accepted solution]

Try these expressions:

sum({<Year={2013}>} total <Month> Sales)

sum({<Year={2012}>} total <Month> Sales)

[jpapador]

Yep was going to add that, Going to still need two expressions if you want separate columns for years

[zagzebski]

Close! - but I need to have one column that is just for 2012 and one for just 2013. If I select 2012 and 2013 it will just add them both in this expression. I need two different expressions.