Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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
Honored Contributor

Re: Link monthly historical average to MonthYear table

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

ecolomer
Honored Contributor II

Re: Link monthly historical average to MonthYear table

Use Month to relation between tables

Not applicable

Re: Link monthly historical average to MonthYear table

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.

s_kunte23
Contributor III

Re: Link monthly historical average to MonthYear table

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
Honored Contributor

Re: Link monthly historical average to MonthYear table

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

Highlighted
Not applicable

Re: Re: Link monthly historical average to MonthYear table

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
Honored Contributor

Re: Link monthly historical average to MonthYear table

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.

s_kunte23
Contributor III

Re: Link monthly historical average to MonthYear table

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
Honored Contributor

Re: Link monthly historical average to MonthYear table

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.

Community Browser