This is the table that I have
| Contract_ID | Period | Forecast | Shortfall |
|---|---|---|---|
| 1 | May23 | 35325 | 456 |
| 2 | June24 | 3466 | 768 |
| 3 | Sept22 | 457657 | 879 |
I need the output as:
| Contract_ID | May23Forecast | May23Shortfall | June24Forecast | June24Shortfall | Sept22Forecast | Sept22Shortfall | Total Forecast | Total Shortfall |
|---|---|---|---|---|---|---|---|---|
| 1 | 35325 | 456 | 35325 | 456 | ||||
| 2 | 3466 | 768 | 3466 | 768 | ||||
| 3 | 457657 | 879 | 457657 | 879 |
In MicroStrategy Developer.
I’m able to achieve this by placing the period attribute on the row level for forecast but I’m not able to add another metric after that.
Any suggestions, anyone?
So, (Not a MicroStrategy Specific Answer)
That said, I always believe that locating business logic closer to the database has benefits.
–If you know the periods (If they are fixed this is easy)
<code>SELECT
, Contract_ID
, CASE WHEN PERIOD = 'May23'
THEN Forecast
ELSE NULL
END AS May23Forecast
, CASE WHEN PERIOD = 'May23'
THEN Shortfall
ELSE NULL
END AS May23Shortfall
, CASE WHEN PERIOD = 'June24'
THEN Forecast
ELSE NULL
END AS June24Forecast
, CASE WHEN PERIOD = 'June24'
THEN Shortfall
ELSE NULL
END AS June24Shortfall
...
FROM YOURTABLE
;
</code>
<code>SELECT
, Contract_ID
, CASE WHEN PERIOD = 'May23'
THEN Forecast
ELSE NULL
END AS May23Forecast
, CASE WHEN PERIOD = 'May23'
THEN Shortfall
ELSE NULL
END AS May23Shortfall
, CASE WHEN PERIOD = 'June24'
THEN Forecast
ELSE NULL
END AS June24Forecast
, CASE WHEN PERIOD = 'June24'
THEN Shortfall
ELSE NULL
END AS June24Shortfall
...
FROM YOURTABLE
;
</code>
SELECT
, Contract_ID
, CASE WHEN PERIOD = 'May23'
THEN Forecast
ELSE NULL
END AS May23Forecast
, CASE WHEN PERIOD = 'May23'
THEN Shortfall
ELSE NULL
END AS May23Shortfall
, CASE WHEN PERIOD = 'June24'
THEN Forecast
ELSE NULL
END AS June24Forecast
, CASE WHEN PERIOD = 'June24'
THEN Shortfall
ELSE NULL
END AS June24Shortfall
...
FROM YOURTABLE
;
Etc.
— IF you are not certain what periods their are, but you know how many (Say 12 a year) something like this can be done
<code>SELECT
Contract_ID
, Period
, MAX(CASE WHEN PERIOD_COUNTER = 1
THEN Forecast
ELSE NULL
END) AS FORCASt1
, MAX(CASE WHEN PERIOD_COUNTER = 1
THEN Shortfall
ELSE NULL
END) AS Shortfall1
, MAX(CASE WHEN PERIOD_COUNTER = 2
THEN Forecast
ELSE NULL
END) AS FORCASt1
, MAX(CASE WHEN PERIOD_COUNTER = 2
THEN Shortfall
ELSE NULL
END) AS Shortfall1
...
FROM YOURTABLE
CROSS JOIN
(SELECT
PERIOD
, ROW_NUMBER() OVER(ORDER BY PERIOD) AS PERIOD_COUNTER
FROM YOUR TABLE
GROUP BY 1
)
;
</code>
<code>SELECT
Contract_ID
, Period
, MAX(CASE WHEN PERIOD_COUNTER = 1
THEN Forecast
ELSE NULL
END) AS FORCASt1
, MAX(CASE WHEN PERIOD_COUNTER = 1
THEN Shortfall
ELSE NULL
END) AS Shortfall1
, MAX(CASE WHEN PERIOD_COUNTER = 2
THEN Forecast
ELSE NULL
END) AS FORCASt1
, MAX(CASE WHEN PERIOD_COUNTER = 2
THEN Shortfall
ELSE NULL
END) AS Shortfall1
...
FROM YOURTABLE
CROSS JOIN
(SELECT
PERIOD
, ROW_NUMBER() OVER(ORDER BY PERIOD) AS PERIOD_COUNTER
FROM YOUR TABLE
GROUP BY 1
)
;
</code>
SELECT
Contract_ID
, Period
, MAX(CASE WHEN PERIOD_COUNTER = 1
THEN Forecast
ELSE NULL
END) AS FORCASt1
, MAX(CASE WHEN PERIOD_COUNTER = 1
THEN Shortfall
ELSE NULL
END) AS Shortfall1
, MAX(CASE WHEN PERIOD_COUNTER = 2
THEN Forecast
ELSE NULL
END) AS FORCASt1
, MAX(CASE WHEN PERIOD_COUNTER = 2
THEN Shortfall
ELSE NULL
END) AS Shortfall1
...
FROM YOURTABLE
CROSS JOIN
(SELECT
PERIOD
, ROW_NUMBER() OVER(ORDER BY PERIOD) AS PERIOD_COUNTER
FROM YOUR TABLE
GROUP BY 1
)
;
New contributor
user5670264 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1