Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Link monthly historical average to MonthYear table

Hi there,

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.

Any ideas?

Thanks,

Untitled.jpg

18 Replies
stabben23
Partner - Master
Partner - Master

Why not remove Plan Date from table? it seams that the avg(field) dont have any connection in your model to Plan Date.

ecolomer
Master II
Master II

Use Month to relation between tables

Not applicable
Author

Hi Staffan,

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.

Anonymous
Not applicable
Author

Hi

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

Best regards

Stefan

stabben23
Partner - Master
Partner - Master

Then concatenate the table into where you have the sales value.

Not applicable
Author

Hi Stefan,

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?

Thanks!

stabben23
Partner - Master
Partner - Master

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.

Anonymous
Not applicable
Author

Hi,

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]

Best regards

Stefan

stabben23
Partner - Master
Partner - Master

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.