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