Average Sales

[shumailh]

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]

please anyone help me.... Thanks in Advance

Shumail

[shumailh]

Anybody please help me on the above message plsssss

[perumal_41]

please send ur application .

[shumailh]

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:

LOAD

AccNo,

Date,

limit,

bal,

delq,

AIF

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

MinMaxDates:

load

minstring(LoadDate_Active) as StartDate,

maxstring(LoadDate_Active) as ReportDate

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

[shumailh]

Can any one help me on this?

[kaushiknsolanki]

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

[shumailh]

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.

http://community.qlik.com/thread/31256

because if i create a seperate field for each previous month then i couldn't compare it in set analysis as per below link.

http://community.qlik.com/message/101556#101556

Shumail