Forum Discussion
Dynamic Transformation to create separate series for charting
Hi : My data source provides me the data in the following format. I need to do some transformation to develop a line chart with 2 series (representing each financial year). Also I need to use different colors for actual and forecast data. Forecast data starts from the month, which is yet to be completed e.g March till June (end of fiscal year)
I want to build something similar but not sure how to show the forecast data points in different color (differentiating from actual data points). Any help would be greatly appreciated. Thanks
Pappu1630 The attached file contains an example of what I believe is what you asked for.
You would need an original Forecast (or Budget) column to begin with. One that might change while the year progresses (like column FC in the picture below). Then, another column contains the Actuals and yet another column calculates the Forecast for the remainder of the year. The formula is shown in the picture, cell D2. Returning NA() for the months that have an actual (except the last one) will cause these data points to be regarded as "empty cells" and, by default, be displayed as gaps. That is, they will not be connected with lines.
7 Replies
Pappu1630 You would need to move the Forecast data into the next column and expand the range of data to be processed to include that column. You would then get
Thanks for your reply. What sort of functions I can apply either in PQ or excel to shape the data into something like below from the data source (input data). When every month finishes I need to pick up forecast value same as the actual value for that period. As shown below I dont need any forecast value prior to last current month that just ended. Regards
Pappu1630 The attached file contains an example of what I believe is what you asked for.
You would need an original Forecast (or Budget) column to begin with. One that might change while the year progresses (like column FC in the picture below). Then, another column contains the Actuals and yet another column calculates the Forecast for the remainder of the year. The formula is shown in the picture, cell D2. Returning NA() for the months that have an actual (except the last one) will cause these data points to be regarded as "empty cells" and, by default, be displayed as gaps. That is, they will not be connected with lines.
