Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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]).
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!
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]).
Thanks a lot Tresesco. It worked for me.