Rank with Date Range in set analysis

[ericdelaqua]

Hello,

I am struggling to work out this scenario and will appreciate some assistance.

I have a file that contains the fields : Model; Designed On; Cost;

the design date include date in the future such as 01/01/2025.

I want to build a straight table with dimension Model and Rank the top 10 models  that were designed in the last year

where the first ranking (1) is the highest sum of cost.

how could this be written in set analysis.

Thanks

Eric

[Anonymous]

The easy way is

1. You can create a new field is YEAR (2015,2016,2017...) when load by script

2. create a variable v_last_year = max(year)-1

3. In set analysis, set year = v_last_year

Then the problem was solved!

[tresesco · Accepted solution]

If you want your last year to be on calendar date then try expression like:

Sum({<Model={"=Rank(Sum({<[Designed On]={[>=$(=YearStart(Today(),-1)) <=$(=YearEnd(Today(),-1))]}>}Cost))<11"}>}Cost)

To make it change on selection, try replacing today() with max([Designed On]).

[ericdelaqua]

Thanks  a lot Tresesco. It worked for me.