As meintoned in given below table, I have sales according to Calendar Week (CW).
Current Year + Calendar Week and Previous Year + Current Week. -> I have master calendar which is connected with this sales data using key Year & '|' & Calendar Week. I don't have dates.
Data Set:
Sales
Calendar Week
Year
1001
CW1
2022
2144
CW2
2022
5342
CW3
2022
2552
CW1
2023
1425
CW2
2023
8791
CW3
2023
Now, I have Dashboard where there are 2 filters:
1. Year (where I can select Year)
2. Calender Week (where I can select Calendar Week)
Need expressions / Expression:
1. How to compare data ? according to calendar filters (Years and Calendar Week). For example, When I select current year 2023 then it must compare the 2022 and 2023 calendar weeks data and show in the table. In a same way when I select Calendar Week then I must see the comparison. Please note, it must work while selecting only calendar weeks also.
Last Year 2022
Current Year 2023
CW
Sales
% Growth
Sales
% Growth
CW1
1001
0
2552
0
CW2
2144
1%
1425
1%
CW3
5342
2%
8791
2%
.
.
.
.
.
.
.
.
.
.
.
.
CW52
n
n
n
n
2. I want to see the data according to current week, previous week and begining of the week. For example:
CW1 -> Beginning Week
CW2 -> Previous Week
CW3 -> Current Week
So, when there is no selection I must see Current, Previous, Beginning Week in Text object.
In future, If I have CW4 then:
CW1 -> Beginning Week
CW3 -> Previous Week
CW4 -> Current Week
Please note: In case of more weeks then it must also reflect to the calendar week selection. For example: if I have 52 weeks of data and I select CW40 then It must reflect to the selection and show the data like given below in the text objects:
CW1 -> Beginning Week
CW39 -> Previous Week
CW40 -> Current Week (selected from the filter Calendar Week)
