Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

shumailh
Contributor III

Average Sales

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

Date1Sales
Dec-09800
Dec-10600
Jan-111000
Feb-111200
Mar-11840
Apr-111200
May-111500
Jun-111800


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 [Smiley Frustrated]

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

Also No Idea on this [Smiley Frustrated]

please anyone help me.... Thanks in Advance

Shumail

6 Replies
Highlighted
shumailh
Contributor III

Average Sales

Anybody please help me on the above message plsssss

perumal_41
Valued Contributor II

Average Sales

please send ur application .

shumailh
Contributor III

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:

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
Contributor III

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

shumailh
Contributor III

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.

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