Link monthly historical average to MonthYear table - Qlik Community

Report Inappropriate Content · ‎2014-10-04

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,

stabben23 · ‎2014-10-04

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

ecolomer · ‎2014-10-04

Use Month to relation between tables

Report Inappropriate Content · ‎2014-10-04

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 · ‎2014-10-04

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 · ‎2014-10-04

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

Report Inappropriate Content · ‎2014-10-04

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 · ‎2014-10-04

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 · ‎2014-10-04

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 · ‎2014-10-04

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.