topic Re: Rank with Date Range in set analysis in QlikView

Rank with Date Range in set analysis

ericdelaqua — Sun, 11 Feb 2018 23:55:00 GMT

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 — Mon, 12 Feb 2018 03:49:41 GMT

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!

tresB — Mon, 12 Feb 2018 04:44:59 GMT

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 — Mon, 12 Feb 2018 05:35:42 GMT

Thanks a lot Tresesco. It worked for me.