Current month average comparision with previous months average

[#1]

Hi All

I have the no of items of particular product for all  months and its average.

i want to claculate the average of the preceding five months and compare it with the current month.

and  if it is greater then show  'Y else 'N'

product	month	items	percent	compare with last 5 months
a	2016 Jan	3152	19.28300502	compare this average with the last five months average if it is greater then 'y' else 'N'
a	2015 Dec	3257	19.925364	compare this average with the last five months average if it is greater then 'y' else 'N'
a	2015 Jul	2489	15.22696684	compare this average with the last five months average if it is greater then 'y' else 'N'
a	2015 Nov	2313	14.15025083	compare this average with the last five months average if it is greater then 'y' else 'N'
a	2015 Aug	2088	12.77376728	compare this average with the last five months average if it is greater then 'y' else 'N'
a	2015 Sep	1784	10.91398507	compare this average with the last five months average if it is greater then 'y' else 'N'
a	2015 Oct	1263	7.726660957	compare this average with the last five months average if it is greater then 'y' else 'N'
b	2015 Jul	7670	15.50246584	compare this average with the last five months average if it is greater then 'y' else 'N'
b	2015 Oct	7126	14.40294284	compare this average with the last five months average if it is greater then 'y' else 'N'
b	2015 Nov	7051	14.25135419	compare this average with the last five months average if it is greater then 'y' else 'N'
b	2016 Jan	6977	14.10178672	compare this average with the last five months average if it is greater then 'y' else 'N'
b	2015 Aug	6961	14.06944781	compare this average with the last five months average if it is greater then 'y' else 'N'
b	2015 Dec	6853	13.85116016	compare this average with the last five months average if it is greater then 'y' else 'N'
b	2015 Sep	6838	13.82084243	compare this average with the last five months average if it is greater then 'y' else 'N'

[sunny_talwar]

Is this needed in the script?

[johnw]

Sometimes I wonder if this is my answer to all questions.

The As-Of Table

I actually don't use them that often, and having two different Month fields can get confusing for selections sometimes. But they're still my favored way for doing things like comparing a month to the five previous months. To briefly summarize the approach, you build a data structure that connects months to other months, and identifies them with a type, or a months back or forward, or that sort of thing.

As Of Month, Month, Months Back

July 2016,July 2016,0

July 2016,June 2016,1

July 2016,May 2016,2

...

July 2016,February 2016,5

August 2016,August 2016,0

...

August 2016, March 2016,5

...

Dimension = As Of Month

Total Items = sum({<[Months Back]={0}>} [Items])

Flag = if([Total Items] > avg(aggr(sum({<[Months Back]={">0<=5"}>} [Items]),[Month])),'Y','N')

Hopefully my syntax is right.

[#4]

Any where would be fine sunil

[#5]

Hi i have already implemented the as of calender i will try this code thank you so much

[#6]

Hi John,

I  have used the below as of calender

[As-Of Calendar]:
Load Month1,
  AsOfMonth,
  Round((AsOfMonth-Month1)*12/365.2425) as MonthDiff,
// Date(Date#(MonthYear,'MMMYYYY'),'YYYYMM') as MonthYearq,
  Year(AsOfMonth)-Year(Month1) as YearDiff
  Resident tmpAsOfCalendar
      Where  AsOfMonth >= Month1;

Drop Table tmpAsOfCalendar;
final:
load *,
If(MonthDiff=0,1,0) as IsSameMonth,
If(MonthDiff<2,1,0) as IsRolling6

Resident [As-Of Calendar];

drop Table [As-Of Calendar];

/////////////////////////////////////////////////

i have flag IsRolling6

i have flag IsSameMonth

///////////////////////////////////////////////////////////////////////////////////////////////////////////

My straight table is like this

                       expression for total                               expression for average

product         =sum({<IsSameMonth={'1'}>}issue)    =sum({<IsSameMonth={'1'}>}issue)

                                                                                  /sum({<IsSameMonth={'1'}>}total(issue)) * 100           Month

Cu                                               3152                                 12.97226109                                            Jan

In1                                              6977                                 28.71429747                                            Jan

In2                                               6207                                 25.54531237                                            Jan

Ot                                                4794                                 19.73001893                                            Jan

Pr                                                 3168                                 13.03811013                                            Jan

Cu                                                 3257                                 13.79850873                                            dec

In1                                                  6853                                 29.03321471                                            dec

In2                                                6310                                 26.73275716                                            dec

Ot                                                 4568                                 19.35265209                                            dec

Pr                                                 2616                                 11.08286731                                            dec

Cu                                                2313                                 9.679444258                                            nov

In1                                                 7051                                 29.50703047                                            nov

In2                                                   7414                                 31.02611316                                            nov

Ot                                                     4266                                 17.85236023                                            nov

Pr                                                      2852                                 11.93505189                                            nov

for a particular product i want  to compare

the last 2 months average = sum(issue) for last 2 months for a particula produt divided by sum(all isuues for the preceding 2 months)

if average is greater then Yes else No

can i use the below expression  to claculate the last 2 months average

=sum({<IsRolling6={'1'}>}issue)/sum({<IsRolling6={'1'}>}total(issue)) * 100

[johnw]

These are minor comments, but when using "total" you don't need to put parentheses around the field. Total is not a function. Also, when calculating percentages, rather than multiplying by 100, change the display format to a percentage on the Number tab. And I'm confused by using flag IsRolling6 to mean "this month and previous month".

As far as the expressions, it looks like you're calculating each product's percentage of the total for the current, or for the current and previous month. It's unclear to me whether the month in your chart is the Month1 or the AsOfMonth, but it should be the AsOfMonth. I'm also unclear how you're getting the percentage for the month with "total(issue)" rather than "total <AsOfMonth> issue". And it's unclear to me why you're calling that percentage an average. So I feel like I'm missing something about what you're doing.

But that aside, your expressions at least demonstrate the right idea about how to reference previous months during the calculation using the AsOf table.

[#8]

Hi John

I have used the below code for my master calender and As-of-calender

Temp: 

Load 

min(Date) as minDate,

max(Date) as maxDate 

Resident keytable; 



Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 



TempCalendar: 

LOAD 

$(varMinDate) + Iterno()-1 As Num, 

Date($(varMinDate) + IterNo() - 1) as TempDate 

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 



MasterCalendar: 

Load 

TempDate AS OrderDate, 

week(TempDate) As Week, 

Year(TempDate) As Year, 

Month(TempDate) As Month, 

Day(TempDate) As Day,
Date(MonthStart(TempDate),'YYYY MMM') as Month1,


YeartoDate(TempDate)*-1 as CurYTDFlag, 

YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

WeekDay(TempDate) as WeekDay



Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar;


tmpAsOfCalendar:
Load distinct Month1  Resident MasterCalendar ;



// ======== Cartesian product with itself ========
Join (tmpAsOfCalendar)
Load Month1 as AsOfMonth
Resident tmpAsOfCalendar ;



// ======== Reload, filter and calculate additional fields ========
[As-Of Calendar]:
Load Month1,
AsOfMonth,
Round((AsOfMonth-Month1)*12/365.2425) as MonthDiff,
// Date(Date#(MonthYear,'MMMYYYY'),'YYYYMM') as MonthYearq,
  Year(AsOfMonth)-Year(Month1) as YearDiff
Resident tmpAsOfCalendar
Where  AsOfMonth >= Month1;

Drop Table tmpAsOfCalendar;
final:
load *,
If(MonthDiff=0,1,0) as IsSameMonth,
If(MonthDiff=0,2,0) as IsSameMonth2,
If(YearDiff=0,1,0) as IsSameYear,
If(MonthDiff<6,1,0) as IsRolling6,
If(MonthDiff=0,5,0) as Is5Month

Resident [As-Of Calendar];

drop Table [As-Of Calendar];

what happend is the association went in reverse order

My month and As-of-month were associated in reverse

so i'm using As-of-month as my list box(where end user selects the date) and i'm using month in the calculation (set analysis) as it is associated in this way

yes i'm calculating each product's percentage of the total for the current month
yes its percentage

can you please correct my as of calender code, it would be of great help for me

and i want  to calculate the average for the previous 5 months, excluding the current month

and compare with the current month.

[johnw · Accepted solution]

I'm not seeing major problems with your calendar, though I wouldn't have linked 2015 Jul to 2016 Jan unless you have a requirement for something 7 months back. If 6 months back is the farthest back you're going like this, no reason to clutter the table with additional connections that you aren't using.

I don't see what you mean by associated in reverse. There are six months for which 2016 Jan is in the past six months, and they are the months you show. And this is equivalent to what you show in the second picture (ignoring the extra 2015 Jul) - one AsOfMonth connected to the past six months.

Your IsSameMonth2 and Is5Month flags are not, I suspect, what you're after. I'm guessing you wanted something like:

if(MonthDiff<2,1,0) as IsRolling2,
if(MonthDiff>0,1,0) as IsPrev5, // if excluding anything earlier from the table, else need an additional check

For the chart, AsOfMonth and Product would be dimensions.

% total of current month = sum({<IsSameMonth={'1'}>} issue) / sum({<IsSameMonth={'1'}>} total <AsOfMonth> issue)
% total of prev 5 months = sum({<IsPrev5={'1'}>} issue) / sum({<IsPrev5={'1'}>} total <AsOfMonth> issue)
Compare = if([% total of current month]>[% total of prev 5 months],'Yes','No')

And this probably isn't needed and probably has bugs, but I wrote it out, so I'll go ahead and include it. Completely untested, but I'd write the script something like this:

// much more efficient than load resident on large tables
[Dates]: 
LOAD 
min([Date]) as [minDate]
,max([Date]) as [maxDate]
;
LOAD date(fieldvalue('Date',recno())) as [Date]
AUTOGENERATE fieldvaluecount('Date')
;
LET varMinDate = peek('minDate'); 
LET varMaxDate = peek('maxDate'); 
DROP TABLE [Dates];

[Master Calendar]: 
LOAD *
,week([OrderDate]) as Week
...
// don't need Month1
,monthstart([OrderDate]) as [MonthYear] // I format in document properties, number tab
...
;
LOAD date($(varMinDate) + recno() - 1) as [OrderDate] 
AUTOGENERATE $(varMaxDate) - $(varMinDate) + 1
;

[AsOf Calendar]:
LOAD *
,-([AsOfMonth]=[MonthYear]) as [IsSameMonth]
,-(year([AsOfMonth])=year([MonthYear]) as [IsSameYear]
,1 as [IsRolling6]
,-([AsOfMonth]<>[MonthYear]) as [IsPrev5]
;
LOAD *
,addmonths([AsOfMonth],1-iterno()) as [MonthYear]
WHILE iterno() <= 6
;
LOAD date(fieldvalue('MonthYear',recno())) as [AsOfMonth]
AUTOGENERATE fieldvaluecount('MonthYear')
;

[#10]

Hi John thank you so much and it resolved the issue.

I was struggling with another issue

Can you please help me out

Top two values in current month comparing the same values with previous month