Qlik Community

Qlik Sense Cloud Discussions

jonnieyacoub
Contributor

Creating variable last 6 months

Hey Qlikkers,

I'm trying to create a variable to show only the last 6 months, but it is not working? currently i'm using this in my script:

datefield

DATE(lead_inserted, 'DD/MM/YYYY') as date,

the not working variable

SET v6LastMonth="=Date(AddMonths(Max(Date),-5),'DD/MM/YYYY')";

Dimension:

=Monthstart(date)

expression:

count({1<[type]={'deal'},

[source]-={'Via verhuurder'},

[date]={'>$(v6LastMonth)'}>}

deal_id)

/

Count({1<[type]={'lead'},

leadsource -={'Via verhuurder'},

[date]={'>$(v6LastMonth)'}>}

lead_id)

Any idea how this could work?

18 Replies
MVP
MVP

Re: Creating variable last 6 months

Modify the quotes:

count({1<[type]={'deal'},

[source]-={'Via verhuurder'},

[date]={">'$(v6LastMonth)'"}>}

deal_id)

/

Count({1<[type]={'lead'},

leadsource -={'Via verhuurder'},

[date]={">'$(v6LastMonth)'"}>}

lead_id)

Double quote the whole date expression and single quote the date itself

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonnieyacoub
Contributor

Re: Creating variable last 6 months

Then it show no result, so i think my variable is not correct? any idea how it should be?

Re: Creating variable last 6 months

How about this

count({1<[type]={'deal'},

[source]-={'Via verhuurder'},

[date]={">$(=$(v6LastMonth))"}>}

deal_id)

/

Count({1<[type]={'lead'},

leadsource -={'Via verhuurder'},

[date]={">$(=$(v6LastMonth))"}>}

lead_id)

jonnieyacoub
Contributor

Re: Creating variable last 6 months

Nope still not working Here is how my script/dimensions/expressions look so far:

Script:

DATE(lead_inserted, 'DD/MM/YYYY') as date,

Variable:

LET vLast6Months = Date(AddMonths(max(Today(date)), -5), 'DD/MM/YYYY');

Dimension:

=MonthName(date)

expression: (added some quotes, but still no result)

count({1<[type]={'deal'},

[source]-={'Via verhuurder'},

[date]={">'$(=$(v6LastMonths))'"}>}

deal_id)

/

Count({1<[type]={'lead'},

leadsource -={'Via verhuurder'},

[date]={">'$(=$(v6LastMonths))'"}>}

lead_id)

Don't you thing there is anything wrong with my script?

Re: Creating variable last 6 months

Did you change from SET to LET? Can you share how your variable look like in variable overview?

jonnieyacoub
Contributor

Re: Creating variable last 6 months

Sunny,

This is my list of variables (all not tested to be honest):

LET vLast6Months = Date(AddMonths(max(Today(date)), -5), 'DD/MM/YYYY');

LET vYearCurrent = Year(Today());

LET vYearPrevious = Year(YearStart(Today())-1);

LET vYearPreviousX2 = Year(YearStart(Today())-2);

LET vMonthCurrent = Month(Today());

LET vMonthPrevious = Month(MonthStart(Today())-1);

LET vMonthPreviousX2 =  Month(MonthStart(Today())-2);

And i only have the standard SET's:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

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

SET TimeFormat='hh:mm:ss';

SET DateFormat='MM/DD/YYYY';

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

SET FirstWeekDay=0;

SET BrokenWeeks=0;

SET ReferenceDay=4;

SET FirstMonthOfYear=1;

SET CollationLocale='en-GB';

SET CreateSearchIndexOnReload=1;

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';

Do i need to include my variables also here? from LET to SET?

Re: Creating variable last 6 months

let's just not worry about the variable right now, can you check what you get when you load them? Image of variable overview?

jonnieyacoub
Contributor

Re: Creating variable last 6 months

no?!? It starts with LeadSourceMap (see picture)

Schermafbeelding 2018-02-01 om 15.01.28.png

Re: Creating variable last 6 months

can you show the definition of vLast6Months

Community Browser