topic Re: Current month average comparision with previous months average in QlikView

Current month average comparision with previous months average

Wed, 25 Nov 2020 16:16:04 GMT

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'

Re: Current month average comparision with previous months average

sunny_talwar — Thu, 13 Oct 2016 17:08:00 GMT

Is this needed in the script?

Re: Current month average comparision with previous months average

johnw — Thu, 13 Oct 2016 17:29:14 GMT

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.

Re: Current month average comparision with previous months average

Fri, 14 Oct 2016 07:52:33 GMT

Any where would be fine sunil

Re: Current month average comparision with previous months average

Fri, 14 Oct 2016 07:53:19 GMT

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

Re: Current month average comparision with previous months average

Sat, 15 Oct 2016 15:11:02 GMT

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

Re: Current month average comparision with previous months average

johnw — Mon, 17 Oct 2016 18:54:07 GMT

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.

Re: Current month average comparision with previous months average

Mon, 17 Oct 2016 19:56:38 GMT

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.

Re: Current month average comparision with previous months average

johnw — Mon, 17 Oct 2016 20:43:31 GMT

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

Re: Current month average comparision with previous months average

Tue, 18 Oct 2016 12:23:23 GMT

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