Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Current month average comparision with previous months average

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'

   

productmonthitemspercentcompare with last 5 months
a2016 Jan315219.28300502compare this average with the last five months average if it is greater then 'y' else 'N'
a2015 Dec325719.925364compare this average with the last five months average if it is greater then 'y' else 'N'
a2015 Jul248915.22696684compare this average with the last five months average if it is greater then 'y' else 'N'
a2015 Nov231314.15025083compare this average with the last five months average if it is greater then 'y' else 'N'
a2015 Aug208812.77376728compare this average with the last five months average if it is greater then 'y' else 'N'
a2015 Sep178410.91398507compare this average with the last five months average if it is greater then 'y' else 'N'
a2015 Oct12637.726660957compare this average with the last five months average if it is greater then 'y' else 'N'
b2015 Jul767015.50246584compare this average with the last five months average if it is greater then 'y' else 'N'
b2015 Oct712614.40294284compare this average with the last five months average if it is greater then 'y' else 'N'
b2015 Nov705114.25135419compare this average with the last five months average if it is greater then 'y' else 'N'
b2016 Jan697714.10178672compare this average with the last five months average if it is greater then 'y' else 'N'
b2015 Aug696114.06944781compare this average with the last five months average if it is greater then 'y' else 'N'
b2015 Dec685313.85116016compare this average with the last five months average if it is greater then 'y' else 'N'
b2015 Sep683813.82084243compare this average with the last five months average if it is greater then 'y' else 'N'
1 Solution

Accepted Solutions
johnw
Champion III
Champion III

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

View solution in original post

9 Replies
sunny_talwar

Is this needed in the script?

johnw
Champion III
Champion III

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.

Not applicable
Author

Any where would be fine sunil

Not applicable
Author

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

Not applicable
Author

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
Champion III
Champion III

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.

Not applicable
Author

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
Champion III
Champion III

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

Not applicable
Author

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