8 Replies Latest reply: Jun 22, 2016 9:35 AM by Arnoux Olivier RSS

    Qlik Sense MTD and YTD

    Arnoux Olivier

      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

        • Re: Qlik Sense MTD and YTD
          Cesar Accardi

          Maybe try like this:

           

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

          • Re: Qlik Sense MTD and YTD
            bruno bertels

            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)

            • Re: Qlik Sense MTD and YTD
              Sangram Reddy

              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.

                • Re: Qlik Sense MTD and YTD
                  Arnoux Olivier

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