6 Replies Latest reply: Jul 6, 2011 8:52 AM by Shumail Hussain

# Average Sales

I am trying to get the average sales on the basis of following date:

 Date1 Sales Dec-09 800 Dec-10 600 Jan-11 1000 Feb-11 1200 Mar-11 840 Apr-11 1200 May-11 1500 Jun-11 1800

I need to have three expressions for averages as follows.

1. Average MTD: (Current Month Sales + Previous Month Sales) / 2

thing is my table is not simple to get the sales for current month i am already using set analysis as below

sum({\$<AIF = {P}, LoadDate_Active = {"=\$(=Date(\$(#=ReportDate),'MM/DD/YYYY'))"}, Bucket_MOM = {0,1,2,3,4,5,6}>} bal)/(1000000*100)

and in order to get the Average MTD I tried this but not working

(sum({\$1<AIF = {P}, LoadDate_Active = {"=\$(=Date(\$(#=ReportDate),'MM/DD/YYYY'))"}, Bucket_MOM = {0,1,2,3,4,5,6}>} bal)/(1000000*100) +

sum({\$<AIF = {P}, LoadDate_Active = {"=\$(=Date(\$(#=ReportDate),'MM/DD/YYYY'))"}, Bucket_MOM = {0,1,2,3,4,5,6}>} bal)/(1000000*100) )/2

OR

(

sum({\$1}{\$<AIF = {P}, LoadDate_Active = {"=\$(=Date(\$(#=ReportDate),'MM/DD/YYYY'))"}, Bucket_MOM = {0,1,2,3,4,5,6}>} bal)/(1000000*100) +

sum({\$<AIF = {P}, LoadDate_Active = {"=\$(=Date(\$(#=ReportDate),'MM/DD/YYYY'))"}, Bucket_MOM = {0,1,2,3,4,5,6}>} bal)/(1000000*100)

)/2

2. Average YTD: (Current Month Sales + Begining of Year Sales) / 2

No Idea how to do it [:S]

3. Average One Year: (Current Month Sales + (12 Months Backward Sales)) / 2

Also No Idea on this [:S]

Shumail

• ###### Average Sales

due to some security issues at my work place i cannot share the file. the thing is i am only having issues with date1 in set analysis.

Script Code:

AccNo,

Date,

limit,

bal,

delq,

AIF

FROM E:\Output\QVDs\ActiveAcc.qvd (qvd);

MinMaxDates:

resident CardMast_MOM;

let ReportDate = peek('ReportDate',0) ;

(

sum({\$<Date1 = {"=\$(=Date(\$(#=ReportDate),'MM/DD/YYYY'))"}>} bal) +

sum({\$<Date1 = {"=\$(=Date(\$(#=ReportDate),'MM/DD/YYYY'))"}>} bal)

)/2

• ###### Average Sales

Can any one help me on this?

• ###### Average Sales

Hi,

I dont know will this work for you or not, but you can try this.

sum({\$<Date1 = {"=\$(=Date(Date#(\$(#=ReportDate),'Give the format in which it is there in field'),'MM/DD/YYYY')"}>} bal)

Please dont forget to enter the same format in which the data is there in field.

So for example if the data is like this  12-Jul-2011 then the expression will be

sum({\$<Date1 = {"=\$(=Date(Date#(\$(#=ReportDate),'DD-MMM-YYYY'),'MM/DD/YYYY')"}>} bal)

Regards,

Kaushik Solanki

• ###### Average Sales

I have no format issues. My query is to get the previous months date variable to compare it with date1 and show the balance for the previous month.

I can only get the current balance by reportdate variable i.e. Jun 11 as below

Date1 = {"=\$(=Date(\$(#=ReportDate),'MM/DD/YYYY'))"}>}

I tried creating a seperate field for each 3 averages but still could not get any solution.