Month over Month w/ Variable in Set Expression.

Mon, 24 Nov 2014 22:01:21 GMT

I am trying to compare the month selected by the user with the month prior to the one they selected. I am not getting any data, but I am also not getting any errors. New to all this and need some help. I am using Qlik Sense.

Expression I am using -

sum( {$<Month={$(=vMonth)},Year=>} Pageviews) +

sum({$<Month={$(=Month(AddMonths(vMonth,-1)))},Year={$(=Year(AddMonths(now(),-1)))}>}Pageviews)

LOAD SCRIPT -

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-US';

SET vMonth = GetFieldSelections(Month)

LOAD

Month(Month)as Month,

dual('Q' & ceil(Month(Month)/3),

ceil(month(Month)/3)) as Quarter,

Year(Month) as Year,

"Day Index",

Pageviews

FROM [lib://Retainer Client Reports/Monthly Retainer Report_Superior Hire.xlsx]

(ooxml, embedded labels, table is [GA - Behaiv_Site_All2]);

Re: Month over Month w/ Variable in Set Expression.

Mon, 24 Nov 2014 22:43:14 GMT

Hey Pete,

Would be helpful if you added more detail on your desired outcome.

A few blind considerations -- You should create a 'MonthYearID' so there is a unique value for each and your expression isn't so convoluted.

(Year(Month) - 1) * 12 + Num(Month(Month)) in the script.

You might want to consider looking into creating a master calendar as well, unless every date is covered in your XLS file.

I believe you need to use the max( function in order to retrieve the last month based on the selection. Think about it from the user perspective -- if they select 2012 and your set mod is month=month -- how does qlikview know what to default to? max() gives you the max value based on what is selected (as long as you use the $ identifier).

topic Re: Month over Month w/ Variable in Set Expression. in QlikView

Month over Month w/ Variable in Set Expression.

Re: Month over Month w/ Variable in Set Expression.