I'm looking for a way to link historical monthly averages to the below table.
Historically in November the rate was 0.596 so for the Plan Date of 1/11/2014 I want add this value.
If Plan Date is removed from the table I do get the historical monthly averages but what I require is to multiply the expected sales value (linked to Plan Date) with the historical average.
in the load statement where you load the data for avg exch. add:
monthstart(MakeDate(Year,Month)) as [Plan Date]
to your script. This will give each year month a date of First date of month and then assiociates your date correctly with Plan Date..
Hope this helps
The averages are not calculated with a load statement. They're just historical daily rates that I converted into averages by means of expression. As for the 'monthstart': I did use that but even without that, I'm still able to convert the forecast (always the first day of the month) into MonthYear.Also that data set is used as Concatenate and thus linked to the main data. It's weird because I thought it would be a pretty straight forward exercise. I have attached the QVD, hope you have some other ideas of might solve it?
U dont have plan date in the same table as DOLLAR thats the main problem. U also have to aggregate DOLLAR to same level as Sales(Saving). if u select 2014 u only have sales on oct/nov/dec and not DOLLAR values in these months.
I'm not 100 % sure as you didn't post the rest of your script. But I think adding this in main is worth a try:
monthstart([Exchange Rate Date]) as [Plan Date]
That will not solve the problem, he will have to do a group by (aggregation) in the script thats calculate the avg(DOLLAR) and "put" it into the first day each month it will than be on same level as Sales. Otherwise he wont get avg of the whole month in table only avg of the first day in month.