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

Month over Month w/ Variable in Set Expression.

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

1 Reply
Not applicable
Author

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).