Set Analysis Current Month vs Previous Month

[jlcoombs]

Hi

I'm having a little bit of trouble getting the result I need having had a look at some similar examples.  I am trying to produce a table that shows the aggregate charges for the current month, and then show what the previous month was.  I have some list boxes with years and months in them to enable me to change which month I'm looking at.

In my table, Current Month is just

=sum({<Style=>}Charge)

My Master Calendar is

MasterCalendar:

LOAD

  TempDate AS LocalTime,

  Date(Floor(TempDate)) as LocalDay,

  Hour(TempDate) AS LocalHour,

  GasDay,

  IF(Hour(TempDate)<=6,Hour(TempDate)+18, Hour(TempDate)-6) AS GasHour,

  Week(GasDay) AS Week,

  Year(GasDay) AS Year,

  Month(GasDay) AS Month,

  num(year(GasDay))*100+num(Month(GasDay)) As year_month_num,

  Day(GasDay) AS Day,

  Weekday(GasDay) AS WeekDay,

  DayNumberOfYear(GasDay) As DayofYear,

  Day(GasDay) & ' ' & Month(GasDay) as DayMonth,

  'Q' & ceil(month(GasDay) / 3) AS Quarter,

  Date(monthstart(GasDay), 'MMM-YYYY') AS MonthYear,

  Week(GasDay)&'-'&Year(GasDay) AS WeekYear,

  inyeartodate(GasDay, $(vToday), 0) * -1 AS CurYTDFlag,

  inyeartodate(GasDay, $(vToday), -1) * -1 AS LastYTDFlag

RESIDENT TempCal2

ORDER BY TempDate ASC;

I added the year_month_num as other examples seemed to indicate this was a good way of doing the month comparison.

For my Previous Month, I thought this would work, but it just returns the same result (I'm not even sure why it does that)

=sum({$<Style=, year_month_num={"=$(=year_month_num-1)"}>}Charge)

Also, I dont think the year_month_num will work when we select January, as this would be 201501 for instance, and 1 less than that is 201500 not 201412.

Can anyone help?

[Gysbert_Wassenaar]

You could use this to create a number field that you can use to calculate the previous month:

num(year(GasDay))*12+num(Month(GasDay)) As year_month_num

Or try MonthStart(GasDay) as GasMonth and use as expression =sum({$<Style=,GasMonth={"=$(MonthStart(max(GasMonth),-1))"}>}Charge)

[jlcoombs · Accepted solution]

I did some more searching after I posted this and have determined a solution.  I used this page as a reference and adapted to suit my scenario

QlikView App: Set Analysis - Prior Period Comparison

First I added to my load script the following

let vPriorMonth  = '=month(addmonths(max(GasDay),-1))';

let vPriorMonthYear = '=Year(addmonths(max(GasDay),-1))';

Then I used this for finding the last month charges

=SUM({<Year={'$(vPriorMonthYear)'},Month={'$(vPriorMonth)'}>}Charge)

I'm sure I could have done it without using the variables but that is the bit that trips me up most easily so as long as it works that suits me.

[miguelbraga]

Hi Jamie,

Take note that when you do this you need to consider the year as well so you basically need to something like this:

=sum({$<

Style=

,Year = {$(=Year(addmonths(max(GasDay),-1)))},

,Month= {$(=(num(month(addmonths(max(date($(=Max(Year)) & '-' & num($(=max(num(Month))),'00') & '-' & '01', 'YYYY-MM-DD')),-1)))))}

  >} Charge)

Hope this solution provide you what you need

[#5]

Hi Jamie Coombs, i'm struggling with this part since the last 1 week

My Requirement is actually this if a user selects the current month the higest 2 values in current month

and i want to see how these higest values are in previous 3 months user can select any month,
if he selects any month the previous 3 months data should appear. please help me out

Table:

LOAD * INLINE [

  Country, MonthYear, Value

  Argentina,Jun2016,550

  Japan,Jun2016,200

  America,Jun2016,100

  America,May2016,300

  Argentina,May2016,250

  Japan,May2016,150

  America,Apr2016,200

  Argentina,Apr2016,170

  Japan,Apr2016,210

];

My output is below

[vatsalajmera5]

Hi,

The easiest solution to this would be to the monthAgo calculation in the Qlik Calender.

create an auto calendar for the date field you are trying to visualize by using the Derive fields function in the calendar script. (  I can attach the complete auto calendar script if needed)

ex:-  DERIVE FIELDS FROM FIELDS [date Field]

now, for current month the code in the measure field would be :

Sum( {$ < [Date.autoCalendar.MonthsAgo] = {'0'}>} sales)

U can change the 0 to 1,2,3 to go back to n last months.

Hope this helps.

[zakpullen]

Hi Vatsal,

Your auto calendar sounds usefule. I'm new to qlikview, and struggling with scripting correctly to get the periods I want to aggregate for (there seem to be so many solutions!).

Many thanks.