Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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' |
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')
;
Is this needed in the script?
Sometimes I wonder if this is my answer to all questions.
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.
Any where would be fine sunil
Hi i have already implemented the as of calender i will try this code thank you so much
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
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.
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.
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')
;
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