Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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])
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)?