hello,
i have a table in which I have a column named Month which is filled based on the date in which I make the refresh (so its filling dynamically) and based on this date I calculate the quarter and the fiscal year (FY). The quarter starts from November and it has 3 months(nov, dec and jan is Q1; feb, mar apr is Q2 and so on). I managed to do this. My issue is that I need to rank these quarters based on the refresh date (the day in which I make the refresh).
i wrote this code but its not working:
select [Month],[Quarter],[FY],
rank() over (partition by [FY] order by [FY] desc )as[Rank_Quarter]
from
[dbo].[Calendar CMD]
it fills the entire column=Rank_Quarters with 1
this is my example:
In table 1 i have the row data.
if I make the refresh in 2020-11, 2020-12 or 2021-01 (which is nov dec or jan) i need to fill in the ranking for the last previous 4 quarters. So being in Q1 it means that the last 4 quarters were from 2019-11 to 2020-10) table2
if I make the refresh in Q2 (febr or march or apr) then I need to rank from 2020-02 until last 2021-01) table3
etc.
So, the bottom line is that based on the refresh date, I have to rank the last 4 closed quarters.
i hope I explained well and maybe someone could help me. i would really appreciate 