Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Like For Like Set Analysis

Hi All,

I trying to do some like for like analysis looking at the previous month and last year and I'm stumped on the syntax.  The data looks something like this:

 

MonthYearLastMonthLastYearsum(Head_Flag)
Sep-2015Aug-2015Sep-20142192
Oct-2015Sep-2015Oct-20142211
Nov-2015Oct-2015Nov-20142207
Dec-2015Nov-2015Dec-20142196
Jan-2016Dec-2015Jan-20152219
Feb-2016Jan-2016Feb-20152224
Mar-2016Feb-2016Mar-20152185
Apr-2016Mar-2016Apr-20152172
May-2016Apr-2016May-20152173
Jun-2016May-2016Jun-20152146
Jul-2016Jun-2016Jul-20152129
Aug-2016Jul-2016Aug-20152109
Sep-2016Aug-2016Sep-20152093

I'm trying to add three additional columns, Headcount last month, Head count last year and Average headcount between last year and current month.

The current month is provided as a selection criteria ultimately resulting in the straight table containing one row of data.

To get last month and Last Years headcount this syntax seems to work as long as none of the dimensions in the straight table are dates:

Last Month:Sum({<MonthYear = P(Previous_MonthYear)>}Head_Flag)

Last Year:Sum({<MonthYear = P(LastYear)>}Head_Flag)

I'm struggling to find the right syntax to do a between statement in the set analysis that refers to LastYear and MonthYear  for the final column.

Any help is much appreciated

6 Replies
sunny_talwar

May be try this:

Last Month

Above(Sum(Head_Flag))

Last Year

Above(Sum(Head_Flag), 12)

Not applicable
Author

Hi Sunny, thanks for the response.  I've had a play with the above function and looked at the documentation.   It certainly works until I make a selection criteria of a specific month at which point the function returns no results.

Before Selection criteria:

MonthYearSum(Head_Flag)above(Sum(Head_Flag))Above(Sum(Head_Flag), 12)
Sep-20152192--
Oct-201522112192-
Nov-201522072211-
Dec-201521962207-
Jan-201622192196-
Feb-201622242219-
Mar-201621852224-
Apr-201621722185-
May-201621732172-
Jun-201621462173-
Jul-201621292146-
Aug-201621092129-
Sep-2016209321092192
Oct-2016209120932211

Once selection criteria is set:

MonthYearSum(Head_Flag)above(Sum(Head_Flag))Above(Sum(Head_Flag), 12)
Sep-20162093--

I was able to get a cumulative total using RangeAvg(Above(Sum(Head_Flag),0,12)) but again this only works if no selection criteria are made.

Thanks again for the assistance

sunny_talwar

Try this:

1 month above

Above(Sum({<MonthYear, Month, Date, Year>}Head_Flag)) * Avg(1)

12 months above

Above(Sum({<MonthYear, Month, Date, Year>}Head_Flag), 12) * Avg(1)


12 month rolling average

RangeAvg(Above(Sum({<MonthYear, Month, Date, Year>}Head_Flag), 0, 12)) * Avg(1)

sunny_talwar

Attaching a sample for you to have a look at. I am only ignoring selection in MonthYear field because I don't have other date and time fields, but you need to ignore all date and time related fields where you can possibly make selections and you don't want them to impact the result of your calculation.

Capture.PNG

Not applicable
Author

Hi Sunny,

Thanks for all the assistance.  I continued reading up on the Above function after your last suggestion and ended up with the following:

Only({<MonthYear=>}Aggr(RangeAvg(Above(Sum({$<MonthYear=>}Head_Flag),0,12)), MonthYear))

Looks pretty similar to your suggestion above.  Can I ask what is the *Avg(1) doing in your example above?

I'm also trying to limit the results to the current month (without the user specifying it via a selection criteria.  I've settled on this code, but it has some issues if the user does actually use a selection criteria you get all the months up to the maximum from the set analysis:

Only({<MonthYear={">=$(=max(MonthYear))"}>}Aggr(RangeAvg(Above(Sum({$<MonthYear=>}Head_Flag),0,12)), MonthYear))

Thanks Again!

sunny_talwar

May be try this:

Only({<MonthYear = {">=$(=Min(MonthYear))<=$(=Max(MonthYear))"}>} Aggr(RangeAvg(Above(Sum({$<MonthYear>} Head_Flag),0,12)), MonthYear))

or this:

Only({<MonthYear = {"$(=Max(MonthYear))"}>} Aggr(RangeAvg(Above(Sum({$<MonthYear>} Head_Flag),0,12)), MonthYear))