How to fill blank values in dimension

gitguto — Fri, 02 Dec 2022 23:35:41 GMT

Hello everyone,

My data looks like this:

Version	jan	fev	mar	apr	may	jun	jul	ago	sep	oct	nov	dec
Historic	450	500	500	550	400	350	200	150	400	500
W01	400	400	400	500	300	300	200	100	350	400	500	450
W30						250	250	100	300	450	400	500
W48											450	550

Every Week, I have a new forecast of what we expect to sell for the remaining of the year (W01 stands for the forecast that came out on week 1, W30 is the forecast that came out on the 30th week of the year, and so forth).

W30 only has values after June because it was released in June and it wouldn't make sense to have a forecast for what already happened. The same applies to W48 which was just released. W01 has values for all months because it was released on the first week of the year and was forecasting the whole year.
The HISTORICAL version is what we really sold each Month.

What I need to do, is to populate the months in the versions that are blank with the historical value.

Desired output would look like this:

Version	jan	fev	mar	apr	may	jun	jul	ago	sep	oct	nov	dec
Historic	450	500	500	550	400	350	200	150	400	500
W01	400	400	400	500	300	300	200	100	350	400	500	450
W30	450	500	500	550	400	250	250	100	300	450	400	500
W48	450	500	500	550	400	350	200	150	400	500	450	550

I need this for a chart that calculates the difference between any 2 selected versions. But as the dimension in this chart is not "Month" (it is some other dimension related to the material that is being sold) I can't do something like:

if(sum({<VERSION = {'vSelectedVersion1'}>}Sells)=0,

sum({<VERISION = {'HISTORICAL'}>}Sells),

sum({<VERSION = {'vSelectedVersion1'}>}Sells))

if(sum({<VERSION = {'vSelectedVersion2'}>}Sells)=0,

sum({<VERISION = {'HISTORICAL'}>}Sells),

sum({<VERSION = {'vSelectedVersion2'}>}Sells))

I can do this both by script or set analysis, whatever works.

Thank you.

topic How to fill blank values in dimension in App Development

How to fill blank values in dimension