topic Re: How to calculate monthly average in a table in QlikView

How to calculate monthly average in a table

anindya_manna — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to calculate monthly average in a table

Anonymous — Tue, 10 Apr 2018 13:54:58 GMT

Can you share the app?

Re: How to calculate monthly average in a table

anindya_manna — Tue, 10 Apr 2018 13:58:39 GMT

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.

Re: How to calculate monthly average in a table

vishsaggi — Tue, 10 Apr 2018 14:31:27 GMT

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')