Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Qlik Sense MTD and YTD

Hi.

I would like to create a KPI with current months data on the left and last year same months calculation on the right within same KPI

Currently I am using the following calculation but it does not seem to be working as both values always shows current filters values.

Left side KPI Measure = Sum(sessions)

Right side KPI measure =

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sessions)


Filters applied = 05/05/2016 ( DD/MM/YYYY)


Can anyone please suggest an alternative formula which will give me the aforementioned result

Tags (1)
8 Replies
cesaraccardi
Valued Contributor

Re: Qlik Sense MTD and YTD

Maybe try like this:

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Max(DateNum)))<=Max(DateNum)))"}>} Sessions)

brunobertels
Valued Contributor

Re: Qlik Sense MTD and YTD

Hi

may be this :

left :

Last MTD

Sum({<Year=, Quarter=, Month=, Week=, DateField={‘>=$(=MonthStart(Max(DateField)))<=$(=AddMonths(Max(DateField)))’}>} Sessions)

Right :

Last MTD, 12 months before

Sum({<Year=, Quarter=, Month=, Week=, DateField={‘>=$(=MonthStart(Max(DateField), -12))<=$(=AddMonths(Max(DateField), -12))’}>} Sessions)

Not applicable

Re: Qlik Sense MTD and YTD

Hi Cesar, Thank you for helping.

Unfortunitely that doesnt work. Right side of KPI always shows zero. And those values for same MTD last year is not zero

Not applicable

Re: Qlik Sense MTD and YTD

Hi Bruno.

This also is not working. shows no values on right side of KPI. only a '-'.

Thanks for helping.

brunobertels
Valued Contributor

Re: Qlik Sense MTD and YTD

hi

may be erase comma before -12 in the right formula :

try this

Right :

Last MTD, 12 months before

Sum({<Year=, Quarter=, Month=, Week=, DateField={‘>=$(=MonthStart(Max(DateField) -12))<=$(=AddMonths(Max(DateField) -12))’}>} Sessions)

reddys310
Honored Contributor II

Re: Qlik Sense MTD and YTD

Hi Arnoux,

I would recommend you to have a MTD and YTD flags in your master calendar as this would reduce the load on the visualizations and also improve your performance.

Make use of inyeartodate() and inmonthtodate() function in your master calendar.

Thanks,

Sangram.

Not applicable

Re: Qlik Sense MTD and YTD

Same result here.

Not applicable

Re: Qlik Sense MTD and YTD

Thank you Sangram. Being new to Qlik Sense. I have never heard of that function.

When trying to run it in the script, it gives me an error.saying the function does not exist.

Could you try and help me out here and maybe explain what these functions are supposed to do?

bi_rhino.inyeartodate does not exist:


My Script so far :

LIB CONNECT TO [BI Server];

[dim_brand]:

LOAD

  [id] AS [brand_id],

  [company] AS [company],

  [url] AS [url],

  [short_id] AS [dim_brand.short_id],

  [brand_name] AS [dim_brand.brand_name],

  [pastel_short_id] AS [pastel_short_id],

  [language] AS [language];

SQL SELECT

`id`,

  `company`,

  `url`,

  `short_id`,

  `brand_name`,

  `pastel_short_id`,

  `language`

FROM `bi_rhino`.`dim_brand`;

LIB CONNECT TO [BI Server];

[dim_date]:

LOAD

  [date_id] AS [date_id],

  [date] AS [date],

  [day] AS [day],

  [day_of_week] AS [day_of_week],

  [day_of_month] AS [day_of_month],

  [day_of_year] AS [day_of_year],

  [weekend] AS [weekend],

  [week_of_year] AS [week_of_year],

  [month] AS [dim_date.month],

  [month_of_year] AS [month_of_year],

  [calendar_quarter] AS [calendar_quarter],

  [calendar_year] AS [calendar_year],

  [financial_quarter] AS [financial_quarter],

  [financial_year] AS [financial_year],

  [holiday] AS [holiday],

  [year_month_id] AS [year_month_id],

  [date_num] AS [date_num];

SQL SELECT

`date_id`,

  `date`,

  `day`,

  `day_of_week`,

  `day_of_month`,

  `day_of_year`,

  `weekend`,

  `week_of_year`,

  `month`,

  `month_of_year`,

  `calendar_quarter`,

  `calendar_year`,

  `financial_quarter`,

  `financial_year`,

  `holiday`,

  `year_month_id`,

  `date_num`,

    inyeartodate (date, today(), 0)

FROM `bi_rhino`.`dim_date`;

LIB CONNECT TO [BI Server];

[dim_language]:

LOAD

  [id] AS [language_id],

  [short_id] AS [dim_language.short_id],

  [short_id_3] AS [short_id_3],

  [name] AS [name];

SQL SELECT

`id`,

  `short_id`,

  `short_id_3`,

  `name`

FROM `bi_rhino`.`dim_language`;

LIB CONNECT TO [BI Server];

[fact_funnelsummary_v2]:

LOAD

  [date_id] AS [date_id],

  [brand_id] AS [brand_id],

  [language_id] AS [language_id],

  [sessions] AS [sessions],

  [paidSessions] AS [paidSessions],

  [unpaidSessions] AS [unpaidSessions],

  [cost] AS [cost],

  [enquiries_normal] AS [enquiries_normal],

  [enquiries_returnToRes] AS [enquiries_returnToRes],

  [bookings] AS [fact_funnelsummary_v2.bookings],

  [sales] AS [sales],

  [GP] AS [GP],

  [target_id] AS [target_id];

SQL SELECT

`date_id`,

  `brand_id`,

  `language_id`,

  `sessions`,

  `paidSessions`,

  `unpaidSessions`,

  `cost`,

  `enquiries_normal`,

  `enquiries_returnToRes`,

  `bookings`,

  `sales`,

  `GP`,

  `target_id`

FROM `bi_rhino`.`fact_funnelsummary_v2`;

LIB CONNECT TO [BI Server];

[targets]:

LOAD

  [brand_id] AS [targets.brand_id],

  [visits_total] AS [visits_total],

  [visits_ppc] AS [visits_ppc],

  [visits_unpaid] AS [visits_unpaid],

  [cpc_us] AS [cpc_us],

  [cpc_zar] AS [cpc_zar],

  [enquiries_total] AS [enquiries_total],

  [enquiries_ras] AS [enquiries_ras],

  [enquiries_res] AS [enquiries_res],

  [bookings] AS [targets.bookings],

  [google_spend_us] AS [google_spend_us],

  [google_spend_zar] AS [google_spend_zar],

  [google_distribution] AS [google_distribution],

  [cpe_us] AS [cpe_us],

  [cpe_zar] AS [cpe_zar],

  [sales_excl_vat] AS [sales_excl_vat],

  [primary_gp] AS [primary_gp],

  [consultants_comm] AS [consultants_comm],

  [merchant_comm] AS [merchant_comm],

  [secondary_gp] AS [secondary_gp],

  [year] AS [year],

  [brand_name] AS [targets.brand_name],

  [month] AS [targets.month],

  [target_id] AS [targets.target_id];

SQL SELECT

`brand_id`,

  `visits_total`,

  `visits_ppc`,

  `visits_unpaid`,

  `cpc_us`,

  `cpc_zar`,

  `enquiries_total`,

  `enquiries_ras`,

  `enquiries_res`,

  `bookings`,

  `google_spend_us`,

  `google_spend_zar`,

  `google_distribution`,

  `cpe_us`,

  `cpe_zar`,

  `sales_excl_vat`,

  `primary_gp`,

  `consultants_comm`,

  `merchant_comm`,

  `secondary_gp`,

  `year`,

  `brand_name`,

  `month`,

  `target_id`

FROM `bi_rhino`.`targets`;

[autoCalendar]:

  DECLARE FIELD DEFINITION Tagged ('$date')

FIELDS

  Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),

  Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter'),

  Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$axis', '$yearquarter'),

  Month($1) AS [Month] Tagged ('$month'),

  Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth'),

  Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber'),

  Date(Floor($1)) AS [Date] Tagged ('$date');

DERIVE FIELDS FROM FIELDS [date] USING [autoCalendar] ;