Forum Discussion

ajpaul's avatar
ajpaul
Frequent Visitor
6 years ago

Can you use RANKX with a date filter?

Hi,

 

I have a dataset that updates every day with the latest results.  I have one table which has a column for date, country and sales value.  Each day will bring new data into this table.  At the moment this data is cumulative so if there was sales value of 10 on day 1 and day 2, the day 2 field would show 20.

 

I have some very simple line and bar chart visualisations showing how sales have grown over time for selected countries.

 

I added a country rank measure using the RANKX method and this did sucessfuly rank the countries however it did it for each date.  I am then using this to filter on my exisitng visualisations using another measure which identifies if the country is in my top 5, 10 e.t.c.  However when I apply it now it does show the top 5 fo each date in my dataset, so for example my top 5 for day 1 could be different to day 2, day 3 e.t.c and I end up with 12 lines on my chart rather than 5.  Ideally what I would like is the ability to rank the countries by cumulative sales as of today -1.  Then have those plotted over time within my charts (even if they were not in the top 5 at certain dates within the chart).

 

All relatively new to this and have tried searching on here for this but couldn't find anything.

 

Thanks a lot in advance for the help!

Need Help

2 Replies

    • ajpaul's avatar
      ajpaul
      Frequent Visitor
      6 years ago

      Hi,

       

      Below is a dummy data that hopefully will help:

       

       UKFranceGermany Rank  
      01/04/202010060 231
      02/04/202020060 231
      03/04/202030060 231
      04/04/202040060 231
      05/04/20205010070 312
      06/04/20206012070 312
      07/04/20207012070 212
      08/04/20208013070 213
      09/04/20209013570 213
      10/04/202010014570 213
      11/04/202011014570 213
      12/04/202011016070 213
      13/04/202011016070 213

       

      As you can see from the above over time the ranking of the countries changes (all sales values are cumulative).  My RANKX is currently producing the rankings as per the rank in the right hand side of the table.  I am then using another measure to return a 1 if the country is in my Top N (I created another table and just populated 1 to 10 in this) or a 0 if it is not.  I then use this measure to filter my visuals.

       

      So at the moment if I used this to show me the Top 1 country by sales over time it would plot 2 lines one for France and one for Germany as they have both been ranked 1 at a particular point in time.  Whereas my aim would be that the RANKX would only produce a rank as at the latest date which would be todays date minus 1 or in the above data set 13/04/2020.  This way it would then only show one line of France.

       

      Not sure if this is possible but I am intrigued to find out.

       

      Thanks.