topic Month over month expression based on selected month in QlikView

Month over month expression based on selected month

Mon, 24 Nov 2014 14:47:32 GMT

I have copied expression below from someone on the community and it works perfectly for what I am trying to do in comparing month over month page views on a website. However, this expression is using current system month whereas I need the first month to be user selected and then the prior month the one before the month selected. How do I update this expression for that to work? Do I need to create a variable in the load script and if so I am not certain how to do that either. I am new to Qlik Sense so I need some hand holding.

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

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

Re: Month over month expression based on selected month

Mon, 24 Nov 2014 14:51:59 GMT

Hi,

Can you try this

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

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

Re: Month over month expression based on selected month

sasikanth — Mon, 24 Nov 2014 14:57:14 GMT

try some thing like below

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

sum({$<Month={$(=max(month)-1)},Year={$(=max(Year)-1)}>}Pageviews)

Re: Month over month expression based on selected month

Mon, 24 Nov 2014 20:22:58 GMT

I tried this and it returns no data. I added a variable to my load script based on another suggestion and alternated the expression. I am still not getting any data however. Please see below.

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 expression based on selected month

sasikanth — Tue, 25 Nov 2014 04:20:20 GMT

hi,

here what is that Month filed , can you please shown us sample grain ,

if date field is "CREATED_DATE" then

LOAD

Month(CREATED_DATE) as Month,

Year(CREATED_DATE) as Year,

FROM ......................;

then use both these fileds Month & Year

in the above expression

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

sum({$<Month={$(=max(month)-1)},Year={$(=max(Year)-1)}>}Pageviews)

Here the expressoin take selected month only

Thaks

Sasi