Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Here is the sample report.
One more question and almost like previous.
I should calculate a four weeks/one month average on week level like this: "Sum(four week sales)/4".
I tried to modify the "12 months"-code, but without success. Should it be something like this?
Variables:
vCurrentMonth = ONLY({$} AsOfMonth)
vCurrentWeek = ONLY({$} WEEK)
Formula:
Sum({1<YEAR ={$(=($(vMyYear)))}, MONTH = {'<= $(=($(vCurrentMonth))) '} >} , WEEK= {'<= $(=($(vCurrentWeek))) '} >} LaskutettuSumma)
+
Sum({1<YEAR ={$(=($(vMyYear))-1)}, MONTH = {'>= $(=($(vCurrentMonth))) '} >}, WEEK= {'>= $(=($(vCurrentWeek))) '} >} LaskutettuSumma)
This obviesly didn't work and neither did the other tricks I've used so far.
The sample file does not have associate logic between week & month. I mean when I select the 1st month it shows (1,2,3,4,5,52,53) - We have more than 4 weeks in the month. And same applies to other months, there are few months without any weeks. So we need fix that issue before we proceed further.
Also trying to understand the actual requirement. Now we need SUM of Weekly Average for LaskutettuSumma. I mean if we select March for example and we wanted the average of LaskutettuSumma across all 4 weeks of March. Is this right? Please correct me If I am wrong.
Cheers - DV
I think it would be easiest to use week. Sum up last 4 weeks sales (LaskutettuSumma) and just divide it with number 4. But this should work also over years, so should the week be eg YYYY-WW (2011-51)? Should some kind of calendar be created for this?
So for example at week 5 we should sum weeks 2-5 and divide that figure with 4 and so on... Here a picture of report.
I was assuming that you are integrating the Weekly Average score in the previous table. The idea of calculating week seems to be right (YYYY-WW). Also it will be good idea to populate a calendar table in the load script.
Cheers - DV
Could you give me a more practical answer, meaning what should I do? Sorry, but I don't know where to start. Planning seems to be easier than implementing plans.
Do you have any idea why choosing only on costcenter from sample.qvw does not affect on columns "Month last year" and "Last 12 months"? And "Last 12 months" total is ok, but there is something wrong with numbers in rows. Sum of rows doesn't match with total.
That is because our expressions are using {1} instead of current selection {$}.
We had used the variable to calculate the vMyYear (Max Year) with the following expression.
Max({1} YEAR) - Where represents the full set of all the records in the application
Also we had used the same logic on Months...
Sum({1<[MONTH]={$(=Only([AsOfMonth]))}, YEAR ={$(=($(vMyYear))-1)} >} LaskutettuSumma)
Hope this helps
Also I will be looking at your previous post sometime soon.
Cheers - DV
Here is a new example file for you...
Hi D V,
Are you busy? Are you able to help with this today or on Monday? Sorry, I just have to ask...
Very sorry! I am working from Client's place. I will look into this over weekend. I hope its okay. Also can you explain me the issue?
Thanks - DV