Showing only a set of dimension values while using rangesum?

[divya_anand]

Hi,

I have this table where "rangesum" is summing up the current and previous values in "w/o rangesum".

I need to find out if it is possible to see only 2016 data on the dimension, but should have the same values in "rangesum" as is now as shown in the second picture?

FYI: the expression behind rangesum is similar to-

=rangesum(above((Count({<cond1,cond2,cond3>} distinct field)),0,RowNo()))

Does anybody have an idea on this?

[dsharmaqv]

extract year from date field and create a year list object(dynamoc option).

like this

=Year(Date#([RCA Period],'YYYY-MMM'))

or you can use write set analysis

[divya_anand]

HI Deepak,

Thank you for the response.

Do you mean creating a list box with =Year(Date#(BDate,'YYYY-MMM')) as expression and selecting 2016 in the listbox?

If this is what you meant, then when I select 2016, this would limit the data only to 2016 and the rangesum would not work as expected i.e, the rangesum would take only the sum of this year but not include the previous years.

Any thoughts?

[sunny_talwar]

Try this:

=RangeSum(Above(Count({<cond1,cond2,cond3>} DISTINCT field), 0, RowNo())) * Avg(1)

[divya_anand]

HI Sunny,

I tried your expression & this is what I get-

This looks good, but how do I see only 2016 data?

[divya_anand]

When I select 2016 in the list box I get this-

but I am trying to achieve this-

To be able to see only 2016 data but should include data from 2015 too

[sunny_talwar]

Ignore selection in Year field here:

=RangeSum(Above(Count({<cond1,cond2,cond3, Year>} DISTINCT field), 0, RowNo())) * Avg(1)

I thought one of your conditions above included Year already

[divya_anand]

Hi Sunny,

Ain't working.

Here is a sample qvw.

[sunny_talwar · Accepted solution]

You want the first expression also?

Just the RangeSum1 expression in itself is working, in case you need the other one... use this expression:

=above((Count({<Year,[BMonth-Year]>} distinct CODE)),0,RowNo()) * Avg(1)

[divya_anand]

Sunny,

This works perfect. However, I have a question.

When we select 2016, the data that is associated with 2016 only has to be available right? How is it that even 2015 data is included on selection of 2016?