Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
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

Highlighted
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

Community Browser