Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Re: Re: Link monthly historical average to MonthYear table

Thanks Stefan,

I don't believe the calendar is the issue e.g. both sets of data (table with historical rates and table with forecast) are linked to the same calendar. The complexity is telling QV to use a period e.g. Oct-14 and link that with the historical averages for the month of October. From the 'Table View' you can see that all dates used are nicely linked together.

Untitled.jpg

stabben23
Honored Contributor

Re: Link monthly historical average to MonthYear table

yes thas correct, but u have concatenate and Plan Date only have a connection to Saving not to DOLLAR.

so when u use the dimension Plan Date u can´t show DOLLAR. Saving is also calculated on Month level and DOLLARS on (almost) every day. If u put in Date, saving and DOLLAR into a tablebox and select 2014, u will se that u dont have any sales before oct and no DOLLAR after end of sept.

2014-10-04_1858.png

stabben23
Honored Contributor

Re: Link monthly historical average to MonthYear table

New_Main:

Load

monthstart(Date) as [Plan Date], avg(DOLLAR) as DOLLAR, avg(EURO) as EURO, avg([EURO Range]) as [Euro Range] Resident Main Group by monthstart(Date); drop table Main;

do a resident Load on Main where u group by on same level as Sales

Highlighted
s_kunte23
Contributor III

Re: Link monthly historical average to MonthYear table

Thats not correct. Monthstart will only give any date value the first day of the month. So 1/26/2014 will be converted to 1/1/2014. Every row is evaluated like this. You therefore don't need group by if you dont want to, the rows are kept. It isessential QV logic you dont need to aggregate Data in your datamodel.

Month start on Exchange Rate Date would therefore work. But the set expression in the average calculation sets Year below 2009, so there won't be any exchange rate when you select a year above that and and add Plan Date as Dimension (DOLLARS in your data model have no plan date values, so qv cant display them). You can see this when you remove Plan Date from your Chart and do not select any year:

I don't know why you are calculating avg of dollars only for values before 2009? This won't work this way when you have a Date Dimension. But maybe you can specify how you want to connect the values to your Plan Dates?

Best regards

Stefan

Not applicable

Re: Link monthly historical average to MonthYear table

Hi Stefan,

Answering your question "I don't know why you are calculating avg of dollars only for values before 2009?" my historical rates run from '99 to '14. Ideally these will be used to plot a forecasted rate in the future which runs parrallell to the forecast in value .e.g. the value for Jan '16 = $3,887 and average Rate in January = 0.586 but that's exactly the problem.

Today I tried to create some variables but that didn't work either. I don't want to create a separate Excel sheet where I enter the average rate similar to the example below. This time I want to use the power of QV.

Untitled.jpg

s_kunte23
Contributor III

Re: Link monthly historical average to MonthYear table

Hi,

Ah Ok. So you want to calculate an average for each month based on the dollar values from 99 to 09 for each month?

Is this fixed for every year or do you want the average using the last x years depending on the selected year?

Can't test this now but I think in your case try to modify your avg calculation like this:

Avg ( total <Month> {< [Plan Date]= , Year = {"<=2009"} >} DOLLARS)

This should even work without modification to your script.

Best regards

Stefan

Not applicable

Re: Link monthly historical average to MonthYear table

"So you want to calculate an average for each month based on the dollar values from 99 to 09 for each month?" Correct. See example below which is based on data by month from '99 to '14.

"Is this fixed for every year or do you want the average using the last x years depending on the selected year?" The average used should be based on the full historical information available.

Untitled.jpg

s_kunte23
Contributor III

Re: Link monthly historical average to MonthYear table

this works with your sample app :

aggr(Avg ( total <Month> {<   Year = , [Plan Date]= >} DOLLAR), Month, [Plan Date])

Hope this helps

Best regards

Stefan

s_kunte23
Contributor III

Re: Link monthly historical average to MonthYear table

Please close this discussion if this solution works for you!

Community Browser