Forum Discussion
Passing a List Query to a Query Parameter
Hi Anonymous ,
I'm hoping I get it this time. The code below creates a list of 9 rows containing the list of quarter start for the recent 9 quarters relative to today's date. Before the final step, only those dates from the current and previous year are selected. This list is then further filtered in the final step such that if today's date is earlier than the first friday of the quarter, only the past quarters are returned.
let list = List.Reverse({0..7}), //count of quarters to be deducted, a total of 8 rows today = Date.From(DateTime.LocalNow()), currentyear = Date.Year(today), firstfridayquarter = //get the date of the first friday of the quarter let dates = List.Dates(Date.StartOfQuarter(today), 7, #duration (1,0,0,0)) //first seven days in List.Select(dates, each Date.DayOfWeekName(_) = "Friday"){0}, //select Friday and return as text firstfridaytotoday = Duration.Days(firstfridayquarter - today), startofmonth = Date.StartOfMonth(today), //start of quarter of today's date quarters = List.Transform(list, each Date.AddQuarters(startofmonth, -_)), selectquarters = List.Select(quarters, each Date.Year(_) >= currentyear - 1 ) //select quarters from last year until current in if today >= firstfridayquarter then selectquarters else List.FirstN(selectquarters, List.Count(selectquarters) - 1)
Hi danextian ,
The database process for Date runs every month close which is the first Friday of the month. However, the report will run every quarter after the month close is processed.
I would like to explain the requirement with the help of scenarios.
Scenario 1: Today is 6th Mar which is the first Fri when the database process runs which implies that the month close process is still not completed.
In this scenario, the report will show the 4 quarters of 2019 i.e. Dec, Sep, Jun and Mar 2019 and the 4 quarters of 2018 i.e. Dec, Sep, Jun and Mar 2018.
Scenario 2: Assume today is 10th Mar 2020 i.e. the month close process is now completed.
In this scenario, the report will show the current quarter of 2020 i.e. Mar 2020 and the 4 quarters of 2019 i.e. Dec, Sep, Jun and Mar 2019.
Scenario 3: Assume today is 10th Jun 2020 i.e. the month close process for Jun 2020 is completed.
In this scenario, the report will show the current quarters of 2020 i.e. Jun and Mar 2020 and the 4 quarters of 2019 i.e. Dec, Sep, Jun and Mar 2019.
The list query needs to dymanically pass only the desired quarters to the parameter for the user to select.
Hope this clarifies. Do let me know if there are additional queries.
Thanks,
Vishy
Hi danextian ,
Just to add, I think what would help is having a list query which will only store the relevant quarters dynamically.
Right now, my list query has all the Dates for each month from 2003 till 2020.
Please correct me if I am wrong.
Thanks,
Vishy
Hi Anonymous ,
A more detailed description right fromt he start would have helped more :). A few more questions:
- How to know if a month has closed?
- Do you follow calendar or fiscal year (when does the fiscal year end)?
Hi danextian ,
Thanks for your prompt replies and yes I should have detailed it out better before.
Please refer to my response besides the question.
- How to know if a month has closed? - Month close happens when the database process runs which is usually the first Friday of the month. But the report will be generated only at the quarter ends. So once the monthly process runs for Mar, Jun, Sep and Dec on the first Fri of those months, the Date column will be loaded with those latest dates as applicable.
- Do you follow calendar or fiscal year (when does the fiscal year end)? - Calendar year is followed. At the end of each quarter month close i.e. Mar, Jun, Sep and Dec (on the first Fri), the report will be generated. So the current year will always be the calendar year and the previous year will be the year prior to the current calendar year.
Hope this clarifies.
Thanks,
Vishy
Hi Anonymous ,
I'm hoping I get it this time. The code below creates a list of 9 rows containing the list of quarter start for the recent 9 quarters relative to today's date. Before the final step, only those dates from the current and previous year are selected. This list is then further filtered in the final step such that if today's date is earlier than the first friday of the quarter, only the past quarters are returned.
let list = List.Reverse({0..7}), //count of quarters to be deducted, a total of 8 rows today = Date.From(DateTime.LocalNow()), currentyear = Date.Year(today), firstfridayquarter = //get the date of the first friday of the quarter let dates = List.Dates(Date.StartOfQuarter(today), 7, #duration (1,0,0,0)) //first seven days in List.Select(dates, each Date.DayOfWeekName(_) = "Friday"){0}, //select Friday and return as text firstfridaytotoday = Duration.Days(firstfridayquarter - today), startofmonth = Date.StartOfMonth(today), //start of quarter of today's date quarters = List.Transform(list, each Date.AddQuarters(startofmonth, -_)), selectquarters = List.Select(quarters, each Date.Year(_) >= currentyear - 1 ) //select quarters from last year until current in if today >= firstfridayquarter then selectquarters else List.FirstN(selectquarters, List.Count(selectquarters) - 1)