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.

[kaushiknsolanki]

Hi,

try this.

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

Regards,

Kaushik Solanki

[richard_chilvers]

Hi

Thanks for responding.

Your expression gives me the total for both years (ie. 2016 + 2017). Seems to ignore the set analysis.

[kaushiknsolanki]

Hi,

It does work for me in my app.

Can you share your application.

Regards,

Kaushik Solanki

[Anil_Babu_Samineni]

How about this?

Alt(Sum(TOTAL <Year>  {<Year={2016}>} Value),0)

[sunny_talwar]

May be you are looking for this

Below(Sum(Value), 12)

or better yet, use The As-Of Table

[richard_chilvers]

This still produces zero values for 2017

[richard_chilvers]

Hi Sunny

Thanks as always for your response. I can see that both your suggestions will probably work, although I haven't tried them.

I felt my expression should work and was hoping that someone would correct an error. I must not understand the purpose of {1} in a set analysis.

[sunny_talwar]

Would be able to point out what your desired output is/was? What you have posted above is what you are getting, but what do you need?

[richard_chilvers]

Hi Sunny

I need the total value for 2016 (ie. 1,250) to appear in the last column of every row.