Set analysis to ignore a dimension

[richard_chilvers]

I have a chart table like this :

Year	Month	Value	Last Year's Total Value
2017	Dec	100	0
2017	Nov	120	0
2017	Oct	150	0
2017	Sep	200	0
....etc.
2016	Dec	120	1,250
2016	Nov	80	1,250
2016	Oct	150	1,250
2016	Sep	90	1,250
etc.....

In the last column, I would like always to have the total value for 2016, but for Year=2017, the column has zeroes.

I am using the expression:

SUM( {1 <Year = {'2016'} > } Value)

So the expression is being affected by the Year dimension. How do I ignore this dimension?

Thanks.

[sunny_talwar]

In that case, kaushik.solanki‌'s response looks good to me... I wonder why it did not work for you

[swuehl · Accepted solution]

What do you see if you put

=SUM( {1 <Year = {'2016'} > } Value)

in a text box?

You should see this value also in the least granular partial sum row in your table chart.

If you do, then Kaushik's expression using the TOTAL qualifier should work.

If not, then please post a sample QVW.

[richard_chilvers]

Hi Stefan

Following on:

I have a variable, vPriorYear = Year-1.

The expression $(vPriorYear) correctly evaluates to the year prior to the year in the dimension on each row of my table.

Please can you advise how I use this variable in set analysis to give the total of the field Value for the year prior to the Year in the dimension?

Your help is appreciated.