topic Average Sales in QlikView

Average Sales

shumailh — Tue, 28 Jun 2011 06:33:05 GMT

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

Average Sales

shumailh — Tue, 28 Jun 2011 09:05:41 GMT

Anybody please help me on the above message plsssss

Average Sales

perumal_41 — Tue, 28 Jun 2011 09:35:47 GMT

please send ur application .

Average Sales

shumailh — Tue, 28 Jun 2011 14:38:17 GMT

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

Average Sales

shumailh — Wed, 06 Jul 2011 12:02:57 GMT

Can any one help me on this?

Average Sales

kaushiknsolanki — Wed, 06 Jul 2011 12:22:30 GMT

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

shumailh — Wed, 06 Jul 2011 12:52:45 GMT

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