topic Re: Set Analysis Current Month vs Previous Month in QlikView

Set Analysis Current Month vs Previous Month

jlcoombs — Fri, 23 Oct 2015 00:54:00 GMT

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?

Re: Set Analysis Current Month vs Previous Month

Gysbert_Wassenaar — Fri, 23 Oct 2015 05:40:43 GMT

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)

Re: Set Analysis Current Month vs Previous Month

jlcoombs — Fri, 23 Oct 2015 05:41:49 GMT

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.

Re: Set Analysis Current Month vs Previous Month

miguelbraga — Fri, 23 Oct 2015 09:08:56 GMT

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

Re: Set Analysis Current Month vs Previous Month

Sun, 09 Oct 2016 16:52:26 GMT

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

Re: Set Analysis Current Month vs Previous Month

vatsalajmera5 — Wed, 24 Jan 2018 23:01:33 GMT

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.

Re: Set Analysis Current Month vs Previous Month

zakpullen — Thu, 24 May 2018 09:26:23 GMT

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.