Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
anindya_manna
Partner
Partner

How to calculate monthly average in a table

Hello Experts,

I have a table given below.I want to calculate average six month sales before campaign start date.Below expression did not give correct result.

avg({$<[Process Date]={">=$(=date(addmonths(CAMPAIGN_START_DATE,-6),'M/D/YYYY')) <$(=date(CAMPAIGN_START_DATE,'M/D/YYYY'))">}[Sales Amt])

Here avg function working such a way like sum(sales amt)/count(sales amt).

But I want avg monthly sales using avg function like sum(sales amt)/no of months.

In the below table for agent 111 total sales is 1500 before six months of CAMPAIGN_START_DATE.no of months will be between 11/11/2017 to 01/01/2018. i.e.

(20+31+1)=52days/30.42=1.70(as 365 days a year so 365/12=30.42)

now avg monthly sales will be 1500/1.70=882.352.

I want like that.any idea how to do that using avg() function.


Process Date, CAMPAIGN_START_DATE, Sales Amt, Agent ID

1/1/2018, 2/1/2018 12:00:00 AM, 500, 111

12/12/2017, 2/1/2018 12:00:00 AM, 500, 111

11/11/2017, 2/1/2018 12:00:00 AM, 500, 111

3/3/2018, 2/1/2018 12:00:00 AM, 400, 111

3 Replies
benazirkhan
Creator II
Creator II

Can you share the app?

anindya_manna
Partner
Partner

sorry.I cant.

Just give me an idea is it possible to calculate by using avg function?

I have calculated by calculating max -min date.

vishsaggi
Champion III
Champion III

May be try this? Created based on your sample data.

Previous1:
LOAD *, Floor(Date#(ProcessDate, 'M/D/YYYY')) AS ProcessDtID,
Floor(Date(Date#(CAMPAIGN_START_DATE, 'M/D/YYYY hh:mm:ss ttt'), 'M/D/YYYY')) AS CampaignDtID,
Date(Date#(CAMPAIGN_START_DATE, 'M/D/YYYY hh:mm:ss ttt'), 'M/D/YYYY') AS CampaignStartDt  INLINE [
ProcessDate, CAMPAIGN_START_DATE, Sales, Agent ID
1/1/2018, 2/1/2018 12:00:00 AM, 500, 111
12/12/2017, 2/1/2018 12:00:00 AM, 500, 111
11/11/2017, 2/1/2018 12:00:00 AM, 500, 111
3/3/2018, 2/1/2018 12:00:00 AM, 400, 111
]
;

Use this

I used a variable to calculate number of days between those 6months.  vNoOfDays

so create a variable using Ctrl+Alt+V then add a variable with below formula.

= Interval(MAX({< ProcessDtID = {">=$(=Floor(AddMonths(Date(Max(CampaignDtID)), -6)))<= $(=Max(CampaignDtID))"} >}ProcessDtID) -
MIN({< ProcessDtID = {">=$(=Floor(AddMonths(Date(Max(CampaignDtID)), -6)))<= $(=Max(CampaignDtID))"} >}ProcessDtID), 'dd') 

Actual Expression:

= Num(Sum({$< ProcessDtID = {">=$(=Floor(AddMonths(Date(Max(CampaignDtID)), -6)))<=$(=Max(CampaignDtID))"} >} [Sales])/Ceil(vNoOfDays/(365/12), '0.05'), '#,##0.00')