Hi All,
I need to show result like below based on selected date in Ascending order.
CY- previous five calendar years from selected date
FY - previous Five finacial years from selected date
Q - all quarters of selected date's Year
Months - all months of selected date's year
Dates - All previous dates of selected date's month
(suppose if i select 04-Jan-2022 in filter it has to show 04- - jan -2022,03- jan - 2022,02 - jan - 2022,01-jan-2022 dates in ascending order)
Above all should show in same report there are so many categories to show in columns so could not create report by increasing canvas size.
It will be great help for me if i get solution, please check below for sample data.
| Price Date (ddmmyyyy) | Price type | Calculated field | price ($) |
| 01-01-2017 | CY | CY-2017 | 3000 |
| 01-01-2018 | CY | CY-2018 | 2500 |
| 01-01-2019 | CY | CY-2019 | 3200 |
| 01-01-2020 | CY | CY-2020 | 4000 |
| 01-01-2021 | CY | CY-2021 | 2700 |
| 01-01-2022 | CY | CY-2022 | 6000 |
| 01-01-2023 | CY | CY-2023 | 5500 |
| 01-04-2017 | FY | FY-2017 | 3500 |
| 01-04-2018 | FY | FY-2018 | 2700 |
| 01-04-2019 | FY | FY-2019 | 3800 |
| 01-04-2020 | FY | FY-2020 | 3900 |
| 01-04-2021 | FY | FY-2021 | 3100 |
| 01-04-2022 | FY | FY-2022 | 5500 |
| 01-01-2021 | Q | Q1-2021 | 1500 |
| 01-04-2021 | Q | Q2-2021 | 1200 |
| 01-07-2021 | Q | Q3-2021 | 1700 |
| 01-10-2021 | Q | Q4-2021 | 2100 |
| 01-01-2022 | Q | Q1-2022 | 1900 |
| 01-04-2022 | Q | Q2-2022 | 1800 |
| 01-07-2022 | Q | Q3-2022 | 1500 |
| 01-10-2022 | Q | Q4-2022 | 1600 |
| 01-01-2022 | M | Jan - 2022 | 400 |
| 01-02-2022 | M | Feb - 2022 | 350 |
| 01-03-2022 | M | Mar - 2022 | 600 |
| 01-04-2022 | M | Apr - 2022 | 360 |
| 01-05-2022 | M | May - 2022 | 400 |
| 01-06-2022 | M | Jun - 2022 | 390 |
| 01-07-2022 | M | July - 2022 | 260 |
| 01-08-2022 | M | Aug -2022 | 300 |
| 01-09-2022 | M | Sep - 2022 | 400 |
| 01-10-2022 | M | Oct -2022 | 410 |
| 01-11-2022 | M | Nov - 2022 | 500 |
| 01-12-2022 | M | Dec - 2022 | 250 |
| 01-01-2022 | D | 01-01-2022 | 100 |
| 01-02-2022 | D | 01-02-2022 | 90 |
| 01-03-2022 | D | 01-03-2022 | 80 |
| 01-04-2022 | D | 01-04-2022 | 110 |
| 01-05-2022 | D | 01-05-2022 | 120 |
| 01-06-2022 | D | 01-06-2022 | 70 |
| 01-07-2022 | D | 01-07-2022 | 60 |
| 01-08-2022 | D | 01-08-2022 | 70 |
| 01-09-2022 | D | 01-09-2022 | 60 |
| 01-10-2022 | D | 01-10-2022 | 150 |
| 01-11-2022 | D | 01-11-2022 | 110 |
| 01-12-2022 | D | 01-12-2022 | 120 |
| 01-13-2023 | D | 01-13-2023 | 140 |
| 01-14-2023 | D | 01-14-2023 | 130 |
| 01-15-2023 | D | 01-15-2023 | 170 |
| 01-16-2023 | D | 01-16-2023 | 100 |
| 01-17-2023 | D | 01-17-2023 | 85 |
| 01-18-2023 | D | 01-18-2023 | 87 |
| 01-19-2023 | D | 01-19-2023 | 92 |
| 01-20-2023 | D | 01-20-2023 | 95 |
| 01-21-2023 | D | 01-21-2023 | 80 |
| 01-22-2023 | D | 01-22-2023 | 70 |
| 01-23-2023 | D | 01-23-2023 | 60 |
| 01-24-2023 | D | 01-24-2023 | 75 |
Suppose, if i select the date 04-01-2022 (ddmmyyyy) rows should be appear like below in ascending order.
Result should be as below,
| Result | Comments |
| Period | Price | |
| Cy-2018 | 2500 | |
| Cy-2019 | 3200 | |
| Cy-2020 | 4000 | |
| Cy-2021 | 2700 | |
| Cy-2022 | 6000 | Calendar year of selected date and Previous four calendar years |
| Fy-2017 | 3500 | |
| Fy-2018 | 2700 | |
| Fy-2019 | 3800 | |
| fy-2020 | 3900 | |
| Fy-2021 | 3100 | Financial year of selected date and Previous four financial years |
| Q1-2022 | 1900 | All quarters in selected date's year if i select other dates |
| Jan-22 | 400 | All Months in selected date's year if i select other dates |
| 01-01-2022 | 100 | |
| 02-01-2022 | 90 | |
| 03-01-2022 | 80 | All previous dates in selected date's month |
Regards,
Kusa
