topic Set Analysis syntax in QlikView

Set Analysis syntax

Anonymous — Tue, 13 Mar 2018 10:22:42 GMT

Hello,

I want to perform a simple task: compare some expression of current month to last year's same month:

I use this set analysis expression to get the sum of the ful month of last year

=sum({$< myDate={'<=$(=date(monthend (addmonths(max(MyDate),-12))))'},

myDate={'>=$(=date(monthstart (addmonths(max(MyDate),-12))))'}

>} [myExpression])

but oddly the value displayed is the same as sum([myExpression])

and I checked that outside the set analysis, date(monthstart (addmonths(max(MyDate),-12))) and date(monthend (addmonths(max(MyDate),-12))) are showing the correct dates.

I think I have something wrong with my syntax, but can't figure it out.

Thanks for any help

Olivier

Re: Set Analysis syntax

petter — Tue, 13 Mar 2018 10:32:55 GMT

You can't have the same field more than once in a set modifier. You specify myDate twice - and that is not allowed and I believe that QlikView normally only takes into consideration the last one.

To combine the criteria you can write it like this:

=sum({$< myDate={'<=$(=date(monthend (addmonths(max(MyDate),-12))))>=$(=date(monthstart (addmonths(max(MyDate),-12))))'}

>} [myExpression])

And secondly it looks like you reference both myDate and MyDate and QlikView is case sensitive so it expects these to be two different fields... I don't know what you think about that should it be one field - then you need to select one or the other variant. Meaning make the single field named myDate or MyDate according to your data model.

Re: Set Analysis syntax

Anil_Babu_Samineni — Tue, 13 Mar 2018 10:33:35 GMT

You cannot write single dimension in 2 parts for analysis tool. You may try like this?

sum({$< myDate={">=$(=date(monthstart (addmonths(max(MyDate),-12)))) <=$(=date(monthend (addmonths(max(MyDate),-12))))'}

>} [myExpression])

Re: Set Analysis syntax

Anonymous — Tue, 13 Mar 2018 18:07:22 GMT

Thanks for this answer (the myDate and MyDate was a typo), but I still cannot manage to have it working.

I streamlined my case in this script (I included all the default SET... part as it may influence the way dates are handled)

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET FirstWeekDay=0;

SET BrokenWeeks=0;

SET ReferenceDay=4;

SET FirstMonthOfYear=1;

SET CollationLocale='en-GB';

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

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

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

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

Load * inline [

mydate,value

01/12/2017, 10

02/12/2017,20

01/12/2016, 30

03/12/2016,40

10/12/2016,50

]

I would like to display this table

	sum(value)	sum(value current month)/sum(value of the month of last year)
dec-17	30	0.25
dec-16	120

Can you help me figure out the set analysis code to get the 0.25 (=30/120)?