i have a source table like with values like this
| 023 Q1 | | | 3 | 1 | 2 | 1 | 3 | | 1 | 11 |
|---|
| 2023 Q3 | | 1 | | 2 | 2 | | | | | 5 |
|---|
| 2023 Q4 | | | | | | | 4 | | 11 | 15 |
|---|
| 2024 Q1 | | | 2 | 2 | 1 | | 1 | | 2 | 8 |
|---|
| 2024 Q2 | 2 | | | 1 | 4 | | | | | 7 |
|---|
| 2024 Q3 | | | | | 2 | 1 | | | | 3 |
|---|
| 2024 Q4 | | | 3 | 10 | 10 | 2 | 20 | 1 | 2 | 48 |
|---|
| 2025 Q1 | 1 | | | 8 | 6 | | 21 | | 4 | 40 |
|---|
| 2025 Q2 | | | | | 4 | | 3 | | | 7 |
|---|
| 2025 Q3 | | | | | 6 | | | | | 6 |
|---|
| 2025 Q4 | | | | 1 | 2 | | 9 | | 1 | 13 |
|---|
| Total | 3 | 1 | 8 | 25 | 39 | 4 | 61 | 1 | 21 | 163 |
|---|
i have written a table transformer macro with the query as
SELECT T1.'Quarter' as Quarter ,
case
when T1.'Quarter'="2023 Q1" then "Mar 31, 2023"
when T1.'Quarter'="2023 Q2" then "Jun 30, 2023"
when T1.'Quarter'="2023 Q3" then "Sep 30, 2023"
when T1.'Quarter'="2023 Q4" then "Dec 31, 2023"
when T1.'Quarter'="2024 Q1" then "Mar 31, 2024"
when T1.'Quarter'="2024 Q2" then "Jun 30, 2024"
when T1.'Quarter'="2024 Q3" then "Sep 30, 2024"
when T1.'Quarter'="2024 Q4" then "Dec 31, 2024"
when T1.'Quarter'="2025 Q1" then "Mar 31, 2025"
when T1.'Quarter'="2025 Q2" then "Jun 30, 2025"
when T1.'Quarter'="2025 Q3" then "Sep 30, 2025"
when T1.'Quarter'="2025 Q4" then "Dec 31, 2025"
end
as 'Quarter End Date',
T1.'Sum of Count AM' as 'AM',
T1.'Sum of Count CFO-GF' as'CFO-GF',
T1.'Sum of Count GCRG' as 'GCRG',
T1.'Sum of Count GWM' as 'GWM',
T1.'Sum of Count NCL' as 'NCL',
T1.'Sum of Count Other' as 'Other',
T1.'Sum of Count P&C & SB' as 'P&C & SB',
T1.'Sum of Count Treasury' as 'Treasury',
T1.'Sum of Count X-Divisional' as'X-divisional'
from T1
As a result i get the below table as result,
| 2023 Q1 | 1680217200000 | | | 3 | 1 | 2 | 1 | 3 | | 1 |
|---|
| 2023 Q3 | 1696028400000 | | 1 | | 2 | 2 | | | | |
|---|
| 2023 Q4 | 1703980800000 | | | | | | | 4 | | 11 |
|---|
| 2024 Q1 | 1711843200000 | | | 2 | 2 | 1 | | 1 | | 2 |
|---|
| 2024 Q2 | 1719702000000 | 2 | | | 1 | 4 | | | | |
|---|
| 2024 Q3 | 1727650800000 | | | | | 2 | 1 | | | |
|---|
| 2024 Q4 | 1735603200000 | | | 3 | 10 | 10 | 2 | 20 | 1 | 2 |
|---|
| 2025 Q1 | 1743375600000 | 1 | | | 8 | 6 | | 21 | | 4 |
|---|
| 2025 Q2 | 1751238000000 | | | | | 4 | | 3 | | |
|---|
| 2025 Q3 | 1759186800000 | | | | | 6 | | | | |
|---|
| 2025 Q4 | 1767139200000 | | | | 1 | 2 | | 9 | | 1 |
|---|
| Total | | 3 | 1 | 8 | 25 | 39 | 4 | 61 | 1 | 21 |
|---|
Quarter End Date column , instead of a date, displays number like 1680217200000.
case
when T1.'Quarter'="2023 Q1" then "Mar 31, 2023"
when T1.'Quarter'="2023 Q2" then "Jun 30, 2023"
when T1.'Quarter'="2023 Q3" then "Sep 30, 2023"
when T1.'Quarter'="2023 Q4" then "Dec 31, 2023"
when T1.'Quarter'="2024 Q1" then "Mar 31, 2024"
when T1.'Quarter'="2024 Q2" then "Jun 30, 2024"
when T1.'Quarter'="2024 Q3" then "Sep 30, 2024"
when T1.'Quarter'="2024 Q4" then "Dec 31, 2024"
when T1.'Quarter'="2025 Q1" then "Mar 31, 2025"
when T1.'Quarter'="2025 Q2" then "Jun 30, 2025"
when T1.'Quarter'="2025 Q3" then "Sep 30, 2025"
when T1.'Quarter'="2025 Q4" then "Dec 31, 2025"
end
as 'Quarter End Date',
i have set the date format as M dd, yy in the setting on the table transformaer macro settings.
could you please let me know how to correct this?
thanks
