cortex-data-foundation: Looker Dashboard Query Error

We are getting the error below when loading the Looker Cortex Data Foundation for SAP - Demand Sensing Dashboards :

{ “data”: { “supports_pivot_in_db”: true, “null_sort_treatment”: “low”, “expired”: false, “ran_at”: “2022-07-14T14:57:47+00:00”, “aggregate_table_used_info”: null, “cached_derived”: true, “runtime”: “0.391”, “added_params”: { “query_timezone”: “Indian/Christmas” }, “forecast_result”: null, “sql”: “-- raw sql results do not include filled-in values for ‘demand_sensing.date_week’\n\n\n– use existing correlation_table_pdt in nn.looker_scratch.LR_8B71C1657801419801_correlation_table_pdt\nSELECT\n (FORMAT_DATE(‘%F’, DATE_TRUNC(demand_sensing.Date , WEEK(MONDAY)))) AS demand_sensing_date_week,\n AVG(CAST((CASE\n WHEN CAST(demand_sensing.Date AS DATE) > CAST (Current_date() AS DATE)\n THEN demand_sensing.DemandPlan\n ELSE\n NULL\n END) AS FLOAT64)) AS average_of_demand_plan,\n AVG(CASE\n WHEN CAST(demand_sensing.Date AS DATE) > CAST (Current_date() AS DATE)\n THEN round( demand_sensing.ForecastQuantity\n )\n ELSE\n NULL\n END) AS demand_sensing_forecast,\n AVG(CASE\n WHEN CAST(demand_sensing.Date AS DATE) > CAST (Current_date() AS DATE)\n THEN round( demand_sensing.ForecastQuantityLowerBound )\n ELSE\n NULL\n END) AS demand_sensing_forecast_lower,\n AVG(CASE\n WHEN CAST(demand_sensing.Date AS DATE) > CAST (Current_date() AS DATE)\n THEN round( demand_sensing.ForecastQuantityUpperBound )\n ELSE\n NULL\n END) AS demand_sensing_forecast_upper,\n AVG(CASE\n WHEN CAST(demand_sensing.Date AS DATE) <= CAST (Current_date() AS DATE)\n THEN round( demand_sensing.Sales )\n ELSE\n NULL\n END) AS demand_sensing_wholesale_quantity_measure\nFROM (WITH\n CalDate AS (\n SELECT\n date,\n EXTRACT (week\n FROM\n date) AS week\n FROM\n UNNEST(GENERATE_DATE_ARRAY(DATE_ADD(current_date(), INTERVAL -cast(5 as INT64) YEAR), DATE_ADD(current_date(), INTERVAL 13 WEEK))) AS date ),\n Grid AS (\n SELECT\n DISTINCT SalesOrders.MaterialNumber_MATNR AS Product,\n SalesOrders.ShipToPartyItem_KUNNR AS Customer,\n SalesOrders.ShipToPartyItemName_KUNNR AS CustomerName,\n SalesOrders.RequestedDeliveryDate_VDATU AS Date,\n Customers.City_ORT01 AS Location,\n Customers.PostalCode_PSTLZ AS PostalCode\n FROM\n nn-agc.cortex_test_reporting.SalesOrders SalesOrders\n LEFT JOIN\n nn-agc.cortex_test_reporting.CustomersMD Customers\n ON\n SalesOrders.Client_MANDT=Customers.Client_MANDT\n AND SalesOrders.ShipToPartyItem_KUNNR=Customers.CustomerNumber_KUNNR\n WHERE\n SalesOrders.Client_MANDT = "200"\n UNION DISTINCT\n SELECT\n DemandForecast.CatalogItemID AS Product,\n DemandForecast.CustomerId AS Customer,\n CustomersMD.Name1_NAME1 AS CustomerName,\n DemandForecast.StartDateOfWeek AS Date,\n CustomersMD.City_ORT01 AS Location,\n CustomersMD.PostalCode_PSTLZ AS PostalCode\n FROM\n nn-agc.cortex_test_reporting.DemandForecastDemandForecast\n INNER JOIN nn-agc.cortex_test_reporting.CustomersMD CustomersMD\n ON CustomersMD.CustomerNumber_KUNNR=DemandForecast.CustomerId\nand CustomersMD.Client_MANDT= "200" ),\n Sales AS (\n SELECT\n SalesOrders.Client_MANDT AS Client_MANDT,\n SalesOrders.MaterialNumber_MATNR AS Product,\n SalesOrders.RequestedDeliveryDate_VDATU AS Date,\n SalesOrders.ShipToPartyItem_KUNNR AS Customer,\n SalesOrders.ShipToPartyItemName_KUNNR AS CustomerName,\n SalesOrders.CumulativeOrderQuantity_KWMENG AS SalesOrderQuantity,\n Customers.City_ORT01 AS Location,\n Customers.CountryKey_LAND1 AS Country,\n Customers.PostalCode_PSTLZ AS PostalCode,\n SUM(SalesOrders.CumulativeOrderQuantity_KWMENG) OVER(PARTITION BY SalesOrders.MaterialNumber_MATNR ORDER BY SalesOrders.RequestedDeliveryDate_VDATU ASC ROWS 13 PRECEDING) AS Past13WeekSales,\n SUM(SalesOrders.CumulativeOrderQuantity_KWMENG) OVER(PARTITION BY SalesOrders.MaterialNumber_MATNR ORDER BY SalesOrders.RequestedDeliveryDate_VDATU ASC ROWS 52 PRECEDING) AS Past52WeekSales,\n FROM\n nn-agc.cortex_test_reporting.SalesOrders SalesOrders\n LEFT JOIN\n nn-agc.cortex_test_reporting.CustomersMD Customers\n ON\n SalesOrders.Client_MANDT=Customers.Client_MANDT\n AND SalesOrders.ShipToPartyItem_KUNNR=Customers.CustomerNumber_KUNNR\n WHERE\n SalesOrders.Client_MANDT= "200" ),\n Forecast AS (\n SELECT\n DemandForecast.CatalogItemID AS Product,\n DemandForecast.StartDateOfWeek AS Date,\n DemandForecast.CustomerId AS Customer,\n DemandForecast.ForecastQuantity AS Sales,\n CustomersMD.City_ORT01 AS Location,\n DemandForecast.ForecastQuantityLowerBound,\n DemandForecast.ForecastQuantityUpperBound\n FROM\n nn-agc.cortex_test_reporting.DemandForecast DemandForecast\n INNER JOIN nn-agc.cortex_test_reporting.CustomersMD CustomersMD\n ON CustomersMD.CustomerNumber_KUNNR=DemandForecast.CustomerId\nand CustomersMD.Client_MANDT= "200"),\n\n DemandPlan AS (\n SELECT\n DemandPlan.MaterialNumber AS Product,\n DemandPlan.WeekStart AS Date,\n DemandPlan.CustomerId AS Customer,\n DemandPlan.DemandPlan AS Sales,\n CustomersMd.City_ORT01 AS Location,\n FROM\n nn-agc.cortex_test_reporting.DemandPlan DemandPlan\n\nINNER JOIN nn-agc.cortex_test_reporting.CustomersMD CustomersMD\n\nON CustomersMD.CustomerNumber_KUNNR=DemandPlan.CustomerId\nand CustomersMD.Client_MANDT= "200" ),\n\n Weather AS(\nSELECT\nWeather.MaxTemp,\nWeather.MinTemp,\nWeather.Country,\nWeather.PostCode,\nWeather.Date,\nWeather.AvgMaxTemp,\nWeather.AvgMinTemp,\nIF\n (COUNT(CASE\n WHEN (MaxTemp>AvgMaxTemp) THEN 1\n END\n ) OVER (PARTITION BY EXTRACT(year FROM Weather.Date),\n EXTRACT(week\n FROM\n Weather.Date),\n Weather.PostCode)>=2,\n TRUE,\n FALSE) AS HeatWave,\n IF\n (COUNT(CASE\n WHEN (MinTemp<AvgMinTemp) THEN 1\n END\n ) OVER (PARTITION BY EXTRACT(year FROM Weather.Date),\n EXTRACT(week\n FROM\n Weather.Date),\n Weather.PostCode)>=2,\n TRUE,\n FALSE) AS ColdFront\nFrom\n(SELECT\n max_temp MaxTemp,\n min_temp MinTemp,\n country Country,\n postcode PostCode,\n date Date,\n AVG(max_temp) OVER(PARTITION BY postcode, extract (week from date) order by extract (week from date) RANGE BETWEEN 20 PRECEDING AND CURRENT ROW) AvgMaxTemp,\n AVG(min_temp) OVER(PARTITION BY postcode, extract (week from date) order by extract (week from date) RANGE BETWEEN 20 PRECEDING AND CURRENT ROW) AvgMinTemp,\nFROM\n nn-agc.cortex_test_reporting.weather_daily) As Weather),\n Trends AS(\n SELECT\n WeekStart,\n Week,\n InterestOverTime,\n Country,\n HierarchyId,\n SearchTerm,\n HistoricalMin,\n HistoricalMax,\n ((InterestOverTime-HistoricalMin)/(HistoricalMax-HistoricalMin))*100 AS NormalizedScore,\n AVG(((InterestOverTime-HistoricalMin)/(HistoricalMax-HistoricalMin))*100) OVER (PARTITION BY Country, HierarchyId )AS AvgNormalizedScore,\n AVG(((InterestOverTime-HistoricalMin)/(HistoricalMax-HistoricalMin))*100) OVER (PARTITION BY Country, HierarchyId ORDER BY Trends.Week RANGE BETWEEN 300 PRECEDING AND CURRENT ROW )AS AvgNormalizedScoreFor10Months,\nFROM (\n SELECT\n WeekStart,\n Extract( WEEK from WeekStart) as Week,\n InterestOverTime,\n CountryCode as Country,\n HierarchyId,\n HierarchyText as SearchTerm,\n MIN(InterestOverTime) OVER (PARTITION BY CountryCode, HierarchyId, EXTRACT(WEEK FROM CAST(WeekStart AS date)) ) AS HistoricalMin,\n MAX(InterestOverTime) OVER (PARTITION BY CountryCode, HierarchyId, EXTRACT(WEEK FROM CAST(WeekStart AS date)) ) AS HistoricalMax\n FROM\n nn-agc.cortex_test_reporting.Trends)Trends\nWHERE\n HistoricalMin != HistoricalMax ),\n Materials as (Select\n MaterialsMD.MaterialText_MAKTX,\n MaterialsMD.MaterialNumber_MATNR,\n MaterialsMD.Client_MANDT,\n ProductHierarchyText.Description_vtext as HierarchyText\n from\n nn-agc.cortex_test_reporting.MaterialsMD MaterialsMD\n left JOIN\n(SELECT\n distinct Hierarchy_Prodh,Client_MANDT,Description_vtext,Level_STUFE,Language_SPRAS\n FROM\n nn-agc.cortex_test_reporting.ProductHierarchiesMD) ProductHierarchyText\n ON left(MaterialsMD.ProductHierarchy_Prdha, 6 ) = ProductHierarchyText.Hierarchy_Prodh\n AND MaterialsMD.Client_MANDT = ProductHierarchyText.Client_MANDT\n AND ProductHierarchyText.Level_STUFE=‘3’\n AND ProductHierarchyText.Language_SPRAS=‘E’\n )\nSELECT\n CalDate.Date,\n CalDate.Week,\n Grid.Product,\n Sales.Client_MANDT,\n materials.MaterialText_MAKTX AS ProductName,\n Grid.CustomerName AS Customer,\n Grid.Location,\n Sales.Country,\n Sales.SalesOrderQuantity AS Sales,\n Sales.Past13WeekSales,\n Sales.Past52WeekSales,\n DemandPlan.Sales DemandPlan,\n ROUND(Forecast.Sales,1) ForecastQuantity,\n ROUND(Forecast.ForecastQuantityLowerBound, 1) AS ForecastQuantityLowerBound,\n ROUND(Forecast.ForecastQuantityUpperBound, 1) AS ForecastQuantityUpperBound,\n Trends.SearchTerm,\n Trends.InterestOverTime,\n ROUND(Weather.MaxTemp,1) AS AverageHighTemperature,\n ROUND(Weather.MinTemp,1) AS AverageLowTemperature,\n ROUND((Weather.MaxTemp+Weather.MinTemp)/2,1) AverageTemperature,\n ROUND(AVG((Weather.MaxTemp+Weather.MinTemp)/2) OVER (PARTITION BY Grid.Location, Grid.Product ORDER BY CalDate.Date ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING ),1) AS MovingAverageTemperature,\n PromotionCalendar.DiscountPercent,\n PromotionCalendar.IsPromo,\n IF(HolidayCalendar.HolidayDate IS NULL,0,1) AS IsHoliday,\n —ForecastOutsideStatisticalRange Impact Score\nIF\n ((DemandPlan.Sales > Forecast.ForecastQuantityUpperBound)\n OR (DemandPlan.Sales < Forecast.ForecastQuantityLowerBound),\n ROUND((ABS(Forecast.Sales-DemandPlan.Sales)/DemandPlan.Sales)100,2),\n 0) AS ForecastOutsideStatisticalRangeImpactScore,\n—HeatWave Impact Score\nIF(HeatWave IS TRUE,\n IF(CorrValue>0.0\n AND (LAG(DemandPlan.sales,1,0)OVER(PARTITION BY Sales.product, Sales.location, EXTRACT(week FROM CalDate.date)\n ORDER BY EXTRACT(week FROM CalDate.date)ASC))>Forecast.ForecastQuantityUpperBound-0.05Forecast.ForecastQuantityUpperBound,\n ROUND((ABS(Forecast.Sales-DemandPlan.Sales)/DemandPlan.Sales)100,2),\n IF(CorrValue<0.0\n And (LAG(DemandPlan.sales,1,0)OVER(PARTITION BY EXTRACT(week FROM CalDate.date)\n ORDER BY EXTRACT(week FROM CalDate.date)ASC))<Forecast.ForecastQuantityLowerBound+0.05Forecast.ForecastQuantityLowerBound,\n ROUND((ABS(Forecast.Sales-DemandPlan.Sales)/DemandPlan.Sales)100,2),0)),0) AS HeatWaveImpactScore,\n\n—ColdFront Impact Score\nIF(ColdFront IS TRUE,\n IF(CorrValue<0.0\n AND (LAG(DemandPlan.sales,1,0)OVER(PARTITION BY Sales.product, Sales.location, EXTRACT(week FROM CalDate.date)\n ORDER BY EXTRACT(week FROM CalDate.date)ASC))>Forecast.ForecastQuantityUpperBound-0.05Forecast.ForecastQuantityUpperBound,\n ROUND((ABS(Forecast.Sales-DemandPlan.Sales)/DemandPlan.Sales)100,2),\n IF(CorrValue>0.0\n And (LAG(DemandPlan.sales,1,0)OVER(PARTITION BY EXTRACT(week FROM CalDate.date)\n ORDER BY EXTRACT(week FROM CalDate.date)ASC))<Forecast.ForecastQuantityLowerBound+0.05Forecast.ForecastQuantityLowerBound,\n ROUND((ABS(Forecast.Sales-DemandPlan.Sales)/DemandPlan.Sales)*100,2),0)),0) AS ColdFrontImpactScore,\n\n — PromoDiffrential Impact Score\nIF\n ( PromotionCalendar.IsPromo=true\n AND ((DemandPlan.Sales-Forecast.Sales)/DemandPlan.Sales) > 0.1,\n ROUND((ABS(Forecast.Sales-DemandPlan.Sales)/DemandPlan.Sales)*100,2),\n 0) AS PromoDiffrentialImpactScore,\n —NonSeasonalTrends Impact Score\nIF\n ((DemandPlan.Sales>Forecast.ForecastQuantityUpperBound\n OR DemandPlan.Sales<Forecast.ForecastQuantityLowerBound)\n AND Trends.Week BETWEEN Trends.Week\n AND Trends.Week+3\n AND ((ABS(NormalizedScore-AvgNormalizedScore)/ AvgNormalizedScore)*100 >0.25),\n ROUND((ABS(NormalizedScore-AvgNormalizedScoreFor10Months)/ AvgNormalizedScoreFor10Months)*100,2),\n 0) AS NonSeasonalTrendsImpactScore,\n\nFROM\n CalDate\nLEFT JOIN\n Grid\nON\n CalDate.date = Grid.date\nLEFT JOIN\n Sales\nON\n CalDate.date = Sales.date\n AND Grid.product = Sales.product\n AND Grid.customer = Sales.customer\n AND Grid.location = Sales.location\nLEFT JOIN\n Forecast\nON\n CalDate.date = Forecast.date\n AND Grid.product = Forecast.product\n AND Grid.customer = Forecast.customer\n AND Grid.location = Forecast.location\nLEFT JOIN\n DemandPlan\nON\n CalDate.date = DemandPlan.date\n AND Grid.product = DemandPlan.product\n AND Grid.customer = DemandPlan.customer\n AND Grid.location = DemandPlan.location\nLEFT JOIN\n Weather\nON\n Grid.PostalCode=Weather.PostCode\n AND CalDate.Date=Weather.Date\nLEFT JOIN\n nn-agc.looker_scratch.LR_8B71C1657801419801_correlation_table_pdt AS CorrelationTable\nON\n Grid.Location=CorrelationTable.Location\n AND Grid.Product=CorrelationTable.product\nLEFT JOIN\n Materials\nON\n Grid.product =Materials.MaterialNumber_MATNR\n AND Materials.Client_MANDT= "200"\n --AND Sales.Client_MANDT=Materials.Client_MANDT\nLEFT JOIN\n Trends\nON\n CalDate.date=Trends.WeekStart\n AND Materials.HierarchyText = Trends.SearchTerm\n AND Sales.Country=Trends.Country\nLEFT JOIN\n nn-agc.cortex_test_reporting.PromotionCalendar PromotionCalendar\nON\n CalDate.date=PromotionCalendar.StartDateOfWeek\n AND Grid.product=PromotionCalendar.CatalogItemId\n AND Grid.customer=PromotionCalendar.Customerid\nLEFT JOIN\n nn-agc.cortex_test_reporting.HolidayCalendar HolidayCalendar\nON\n Grid.Date = HolidayCalendar.HolidayDate)\n AS demand_sensing\nWHERE ((( demand_sensing.Date ) >= ((DATE_ADD(CURRENT_DATE(‘Indian/Christmas’), INTERVAL -89 DAY))) AND ( demand_sensing.Date ) < ((DATE_ADD(DATE_ADD(CURRENT_DATE(‘Indian/Christmas’), INTERVAL -89 DAY), INTERVAL 90 DAY)))))\nGROUP BY\n 1\nORDER BY\n 1 DESC\nLIMIT 500”, “sql_explain”: null, “fields”: { “measures”: [ { “align”: “right”, “can_filter”: true, “category”: “measure”, “default_filter_value”: null, “description”: “”, “enumerations”: null, “field_group_label”: null, “fill_style”: null, “fiscal_month_offset”: 0, “has_allowed_values”: false, “hidden”: false, “is_filter”: false, “is_numeric”: true, “label”: “Average of Demand Plan”, “label_from_parameter”: null, “label_short”: “Average of Demand Plan”, “map_layer”: null, “name”: “average_of_demand_plan”, “strict_value_format”: false, “requires_refresh_on_sort”: false, “sortable”: true, “suggestions”: null, “tags”: [], “type”: “average”, “user_attribute_filter_types”: [ “number”, “advanced_filter_number” ], “value_format”: null, “view”: “demand_sensing”, “view_label”: “”, “dynamic”: true, “week_start_day”: “monday”, “original_view”: “demand_sensing”, “dimension_group”: null, “error”: null, “field_group_variant”: “Average of Demand Plan”, “measure”: true, “parameter”: false, “primary_key”: false, “project_name”: “marketplace_cortex-sap-demand-sensing”, “scope”: “demand_sensing”, “suggest_dimension”: “average_of_demand_plan”, “suggest_explore”: “demand_sensing”, “suggestable”: false, “is_fiscal”: false, “is_timeframe”: false, “can_time_filter”: false, “time_interval”: null, “lookml_link”: null, “permanent”: null, “source_file”: “imported_projects/cortex-sap-demand-sensing/views/demand_sensing.view.lkml”, “source_file_path”: “marketplace_cortex-sap-demand-sensing/imported_projects/cortex-sap-demand-sensing/views/demand_sensing.view.lkml”, “sql”: “average_of_demand_plan”, “sql_case”: null, “filters”: null, “times_used”: 0 }, { “align”: “right”, “can_filter”: true, “category”: “measure”, “default_filter_value”: null, “description”: “”, “enumerations”: null, “field_group_label”: null, “fill_style”: null, “fiscal_month_offset”: 0, “has_allowed_values”: false, “hidden”: false, “is_filter”: false, “is_numeric”: true, “label”: “Demand Sensing Forecast”, “label_from_parameter”: null, “label_short”: “Forecast”, “map_layer”: null, “name”: “demand_sensing.forecast”, “strict_value_format”: false, “requires_refresh_on_sort”: false, “sortable”: true, “suggestions”: null, “tags”: [], “type”: “average”, “user_attribute_filter_types”: [ “number”, “advanced_filter_number” ], “value_format”: null, “view”: “demand_sensing”, “view_label”: “Demand Sensing”, “dynamic”: false, “week_start_day”: “monday”, “original_view”: “demand_sensing”, “dimension_group”: null, “error”: null, “field_group_variant”: “Forecast”, “measure”: true, “parameter”: false, “primary_key”: false, “project_name”: “marketplace_cortex-sap-demand-sensing”, “scope”: “demand_sensing”, “suggest_dimension”: “demand_sensing.forecast”, “suggest_explore”: “demand_sensing”, “suggestable”: false, “is_fiscal”: false, “is_timeframe”: false, “can_time_filter”: false, “time_interval”: null, “lookml_link”: “/projects/marketplace_cortex-sap-demand-sensing/files/imported_projects%2Fcortex-sap-demand-sensing%2Fviews%2Fdemand_sensing.view.lkml?line=504”, “permanent”: null, “source_file”: “imported_projects/cortex-sap-demand-sensing/views/demand_sensing.view.lkml”, “source_file_path”: “marketplace_cortex-sap-demand-sensing/imported_projects/cortex-sap-demand-sensing/views/demand_sensing.view.lkml”, “sql”: “CASE\n WHEN CAST(${TABLE}.Date AS DATE) > CAST (Current_date() AS DATE)\n THEN round(${forecast_quantity})\n ELSE\n NULL\n END”, “sql_case”: null, “filters”: null, “times_used”: 0 }, { “align”: “right”, “can_filter”: true, “category”: “measure”, “default_filter_value”: null, “description”: “”, “enumerations”: null, “field_group_label”: null, “fill_style”: null, “fiscal_month_offset”: 0, “has_allowed_values”: false, “hidden”: false, “is_filter”: false, “is_numeric”: true, “label”: “Demand Sensing Forecast Lower”, “label_from_parameter”: null, “label_short”: “Forecast Lower”, “map_layer”: null, “name”: “demand_sensing.forecast_lower”, “strict_value_format”: false, “requires_refresh_on_sort”: false, “sortable”: true, “suggestions”: null, “tags”: [], “type”: “average”, “user_attribute_filter_types”: [ “number”, “advanced_filter_number” ], “value_format”: null, “view”: “demand_sensing”, “view_label”: “Demand Sensing”, “dynamic”: false, “week_start_day”: “monday”, “original_view”: “demand_sensing”, “dimension_group”: null, “error”: null, “field_group_variant”: “Forecast Lower”, “measure”: true, “parameter”: false, “primary_key”: false, “project_name”: “marketplace_cortex-sap-demand-sensing”, “scope”: “demand_sensing”, “suggest_dimension”: “demand_sensing.forecast_lower”, “suggest_explore”: “demand_sensing”, “suggestable”: false, “is_fiscal”: false, “is_timeframe”: false, “can_time_filter”: false, “time_interval”: null, “lookml_link”: “/projects/marketplace_cortex-sap-demand-sensing/files/imported_projects%2Fcortex-sap-demand-sensing%2Fviews%2Fdemand_sensing.view.lkml?line=515”, “permanent”: null, “source_file”: “imported_projects/cortex-sap-demand-sensing/views/demand_sensing.view.lkml”, “source_file_path”: “marketplace_cortex-sap-demand-sensing/imported_projects/cortex-sap-demand-sensing/views/demand_sensing.view.lkml”, “sql”: “CASE\n WHEN CAST(${TABLE}.Date AS DATE) > CAST (Current_date() AS DATE)\n THEN round(${forecast_quantity_lower_bound})\n ELSE\n NULL\n END”, “sql_case”: null, “filters”: null, “times_used”: 0 }, { “align”: “right”, “can_filter”: true, “category”: “measure”, “default_filter_value”: null, “description”: “”, “enumerations”: null, “field_group_label”: null, “fill_style”: null, “fiscal_month_offset”: 0, “has_allowed_values”: false, “hidden”: false, “is_filter”: false, “is_numeric”: true, “label”: “Demand Sensing Forecast Upper”, “label_from_parameter”: null, “label_short”: “Forecast Upper”, “map_layer”: null, “name”: “demand_sensing.forecast_upper”, “strict_value_format”: false, “requires_refresh_on_sort”: false, “sortable”: true, “suggestions”: null, “tags”: [], “type”: “average”, “user_attribute_filter_types”: [ “number”, “advanced_filter_number” ], “value_format”: null, “view”: “demand_sensing”, “view_label”: “Demand Sensing”, “dynamic”: false, “week_start_day”: “monday”, “original_view”: “demand_sensing”, “dimension_group”: null, “error”: null, “field_group_variant”: “Forecast Upper”, “measure”: true, “parameter”: false, “primary_key”: false, “project_name”: “marketplace_cortex-sap-demand-sensing”, “scope”: “demand_sensing”, “suggest_dimension”: “demand_sensing.forecast_upper”, “suggest_explore”: “demand_sensing”, “suggestable”: false, “is_fiscal”: false, “is_timeframe”: false, “can_time_filter”: false, “time_interval”: null, “lookml_link”: “/projects/marketplace_cortex-sap-demand-sensing/files/imported_projects%2Fcortex-sap-demand-sensing%2Fviews%2Fdemand_sensing.view.lkml?line=525”, “permanent”: null, “source_file”: “imported_projects/cortex-sap-demand-sensing/views/demand_sensing.view.lkml”, “source_file_path”: “marketplace_cortex-sap-demand-sensing/imported_projects/cortex-sap-demand-sensing/views/demand_sensing.view.lkml”, “sql”: “CASE\n WHEN CAST(${TABLE}.Date AS DATE) > CAST (Current_date() AS DATE)\n THEN round(${forecast_quantity_upper_bound})\n ELSE\n NULL\n END”, “sql_case”: null, “filters”: null, “times_used”: 0 }, { “align”: “right”, “can_filter”: true, “category”: “measure”, “default_filter_value”: null, “description”: “”, “enumerations”: null, “field_group_label”: null, “fill_style”: null, “fiscal_month_offset”: 0, “has_allowed_values”: false, “hidden”: false, “is_filter”: false, “is_numeric”: true, “label”: “Demand Sensing Wholesale Quantity Measure”, “label_from_parameter”: null, “label_short”: “Wholesale Quantity Measure”, “map_layer”: null, “name”: “demand_sensing.wholesale_quantity_measure”, “strict_value_format”: false, “requires_refresh_on_sort”: false, “sortable”: true, “suggestions”: null, “tags”: [], “type”: “average”, “user_attribute_filter_types”: [ “number”, “advanced_filter_number” ], “value_format”: null, “view”: “demand_sensing”, “view_label”: “Demand Sensing”, “dynamic”: false, “week_start_day”: “monday”, “original_view”: “demand_sensing”, “dimension_group”: null, “error”: null, “field_group_variant”: “Wholesale Quantity Measure”, “measure”: true, “parameter”: false, “primary_key”: false, “project_name”: “marketplace_cortex-sap-demand-sensing”, “scope”: “demand_sensing”, “suggest_dimension”: “demand_sensing.wholesale_quantity_measure”, “suggest_explore”: “demand_sensing”, “suggestable”: false, “is_fiscal”: false, “is_timeframe”: false, “can_time_filter”: false, “time_interval”: null, “lookml_link”: “/projects/marketplace_cortex-sap-demand-sensing/files/imported_projects%2Fcortex-sap-demand-sensing%2Fviews%2Fdemand_sensing.view.lkml?line=493”, “permanent”: null, “source_file”: “imported_projects/cortex-sap-demand-sensing/views/demand_sensing.view.lkml”, “source_file_path”: “marketplace_cortex-sap-demand-sensing/imported_projects/cortex-sap-demand-sensing/views/demand_sensing.view.lkml”, “sql”: “CASE\n WHEN CAST(${TABLE}.Date AS DATE) <= CAST (Current_date() AS DATE)\n THEN round(${order_quantity})\n ELSE\n NULL\n END”, “sql_case”: null, “filters”: null, “times_used”: 0 } ], “dimensions”: [ { “align”: “left”, “can_filter”: true, “category”: “dimension”, “default_filter_value”: null, “description”: “”, “enumerations”: null, “field_group_label”: “Date Date”, “fill_style”: “range”, “fiscal_month_offset”: 0, “has_allowed_values”: false, “hidden”: false, “is_filter”: false, “is_numeric”: false, “label”: “Demand Sensing Date Week”, “label_from_parameter”: null, “label_short”: “Date Week”, “map_layer”: null, “name”: “demand_sensing.date_week”, “strict_value_format”: false, “requires_refresh_on_sort”: false, “sortable”: true, “suggestions”: null, “tags”: [], “type”: “date_week”, “user_attribute_filter_types”: [ “datetime”, “advanced_filter_datetime” ], “value_format”: null, “view”: “demand_sensing”, “view_label”: “Demand Sensing”, “dynamic”: false, “week_start_day”: “monday”, “original_view”: “demand_sensing”, “dimension_group”: “demand_sensing.date”, “error”: null, “field_group_variant”: “Week”, “measure”: false, “parameter”: false, “primary_key”: false, “project_name”: “marketplace_cortex-sap-demand-sensing”, “scope”: “demand_sensing”, “suggest_dimension”: “demand_sensing.date_week”, “suggest_explore”: “demand_sensing”, “suggestable”: false, “is_fiscal”: false, “is_timeframe”: true, “can_time_filter”: false, “time_interval”: { “name”: “week”, “count”: 1 }, “lookml_link”: “/projects/marketplace_cortex-sap-demand-sensing/files/imported_projects%2Fcortex-sap-demand-sensing%2Fviews%2Fdemand_sensing.view.lkml?line=374”, “permanent”: null, “source_file”: “imported_projects/cortex-sap-demand-sensing/views/demand_sensing.view.lkml”, “source_file_path”: “marketplace_cortex-sap-demand-sensing/imported_projects/cortex-sap-demand-sensing/views/demand_sensing.view.lkml”, “sql”: "${TABLE}.Date ", “sql_case”: null, “filters”: null, “times_used”: 0, “sorted”: { “sort_index”: 0, “desc”: true } } ], “table_calculations”: [], “pivots”: [] }, “fill_fields”: [ “demand_sensing.date_week” ], “has_totals”: false, “has_row_totals”: false, “applied_filters”: { “demand_sensing.date_date”: { “field”: { “align”: “left”, “can_filter”: true, “category”: “dimension”, “default_filter_value”: null, “description”: “”, “enumerations”: null, “field_group_label”: “Date Date”, “fill_style”: “range”, “fiscal_month_offset”: 0, “has_allowed_values”: false, “hidden”: false, “is_filter”: false, “is_numeric”: false, “label”: “Demand Sensing Date Date”, “label_from_parameter”: null, “label_short”: “Date Date”, “map_layer”: null, “name”: “demand_sensing.date_date”, “strict_value_format”: false, “requires_refresh_on_sort”: false, “sortable”: true, “suggestions”: null, “tags”: [], “type”: “date_date”, “user_attribute_filter_types”: [ “datetime”, “advanced_filter_datetime” ], “value_format”: null, “view”: “demand_sensing”, “view_label”: “Demand Sensing”, “dynamic”: false, “week_start_day”: “monday”, “original_view”: “demand_sensing”, “dimension_group”: “demand_sensing.date”, “error”: null, “field_group_variant”: “Date”, “measure”: false, “parameter”: false, “primary_key”: false, “project_name”: “marketplace_cortex-sap-demand-sensing”, “scope”: “demand_sensing”, “suggest_dimension”: “demand_sensing.date_date”, “suggest_explore”: “demand_sensing”, “suggestable”: false, “is_fiscal”: false, “is_timeframe”: true, “can_time_filter”: false, “time_interval”: { “name”: “day”, “count”: 1 }, “lookml_link”: “/projects/marketplace_cortex-sap-demand-sensing/files/imported_projects%2Fcortex-sap-demand-sensing%2Fviews%2Fdemand_sensing.view.lkml?line=374”, “permanent”: null, “source_file”: “imported_projects/cortex-sap-demand-sensing/views/demand_sensing.view.lkml”, “source_file_path”: “marketplace_cortex-sap-demand-sensing/imported_projects/cortex-sap-demand-sensing/views/demand_sensing.view.lkml”, “sql”: "${TABLE}.Date ", “sql_case”: null, “filters”: null, “times_used”: 0 }, “value”: “90 days” } }, “applied_filter_expression”: null, “number_format”: “1,234.56”, “explore”: { “name”: “demand_sensing”, “label”: “Demand Sensing”, “description”: null }, “timezone”: “Indian/Christmas”, “data”: [], “errors”: [ { “message”: “The Google BigQuery Standard SQL database encountered an error while running this query.”, “message_details”: “Query execution failed: - Not found: Table nn-agc:cortex_test_reporting.DemandForecast was not found in location US”, “params”: “-- raw sql results do not include filled-in values for ‘demand_sensing.date_week’\n\n\n– use existing correlation_table_pdt in nn-agc.looker_scratch.LR_8B71C1657801419801_correlation_table_pdt\nSELECT\n (FORMAT_DATE(‘%F’, DATE_TRUNC(demand_sensing.Date , WEEK(MONDAY)))) AS demand_sensing_date_week,\n AVG(CAST((CASE\n WHEN CAST(demand_sensing.Date AS DATE) > CAST (Current_date() AS DATE)\n THEN demand_sensing.DemandPlan\n ELSE\n NULL\n END) AS FLOAT64)) AS average_of_demand_plan,\n AVG(CASE\n WHEN CAST(demand_sensing.Date AS DATE) > CAST (Current_date() AS DATE)\n THEN round( demand_sensing.ForecastQuantity\n )\n ELSE\n NULL\n END) AS demand_sensing_forecast,\n AVG(CASE\n WHEN CAST(demand_sensing.Date AS DATE) > CAST (Current_date() AS DATE)\n THEN round( demand_sensing.ForecastQuantityLowerBound )\n ELSE\n NULL\n END) AS demand_sensing_forecast_lower,\n AVG(CASE\n WHEN CAST(demand_sensing.Date AS DATE) > CAST (Current_date() AS DATE)\n THEN round( demand_sensing.ForecastQuantityUpperBound )\n ELSE\n NULL\n END) AS demand_sensing_forecast_upper,\n AVG(CASE\n WHEN CAST(demand_sensing.Date AS DATE) <= CAST (Current_date() AS DATE)\n THEN round( demand_sensing.Sales )\n ELSE\n NULL\n END) AS demand_sensing_wholesale_quantity_measure\nFROM (WITH\n CalDate AS (\n SELECT\n date,\n EXTRACT (week\n FROM\n date) AS week\n FROM\n UNNEST(GENERATE_DATE_ARRAY(DATE_ADD(current_date(), INTERVAL -cast(5 as INT64) YEAR), DATE_ADD(current_date(), INTERVAL 13 WEEK))) AS date ),\n Grid AS (\n SELECT\n DISTINCT SalesOrders.MaterialNumber_MATNR AS Product,\n SalesOrders.ShipToPartyItem_KUNNR AS Customer,\n SalesOrders.ShipToPartyItemName_KUNNR AS CustomerName,\n SalesOrders.RequestedDeliveryDate_VDATU AS Date,\n Customers.City_ORT01 AS Location,\n Customers.PostalCode_PSTLZ AS PostalCode\n FROM\n nn-agc.cortex_test_reporting.SalesOrders SalesOrders\n LEFT JOIN\n nn-agc.cortex_test_reporting.CustomersMD Customers\n ON\n SalesOrders.Client_MANDT=Customers.Client_MANDT\n AND SalesOrders.ShipToPartyItem_KUNNR=Customers.CustomerNumber_KUNNR\n WHERE\n SalesOrders.Client_MANDT = "200"\n UNION DISTINCT\n SELECT\n DemandForecast.CatalogItemID AS Product,\n DemandForecast.CustomerId AS Customer,\n CustomersMD.Name1_NAME1 AS CustomerName,\n DemandForecast.StartDateOfWeek AS Date,\n CustomersMD.City_ORT01 AS Location,\n CustomersMD.PostalCode_PSTLZ AS PostalCode\n FROM\n nn-agc.cortex_test_reporting.DemandForecastDemandForecast\n INNER JOIN nn-agc.cortex_test_reporting.CustomersMD CustomersMD\n ON CustomersMD.CustomerNumber_KUNNR=DemandForecast.CustomerId\nand CustomersMD.Client_MANDT= "200" ),\n Sales AS (\n SELECT\n SalesOrders.Client_MANDT AS Client_MANDT,\n SalesOrders.MaterialNumber_MATNR AS Product,\n SalesOrders.RequestedDeliveryDate_VDATU AS Date,\n SalesOrders.ShipToPartyItem_KUNNR AS Customer,\n SalesOrders.ShipToPartyItemName_KUNNR AS CustomerName,\n SalesOrders.CumulativeOrderQuantity_KWMENG AS SalesOrderQuantity,\n Customers.City_ORT01 AS Location,\n Customers.CountryKey_LAND1 AS Country,\n Customers.PostalCode_PSTLZ AS PostalCode,\n SUM(SalesOrders.CumulativeOrderQuantity_KWMENG) OVER(PARTITION BY SalesOrders.MaterialNumber_MATNR ORDER BY SalesOrders.RequestedDeliveryDate_VDATU ASC ROWS 13 PRECEDING) AS Past13WeekSales,\n SUM(SalesOrders.CumulativeOrderQuantity_KWMENG) OVER(PARTITION BY SalesOrders.MaterialNumber_MATNR ORDER BY SalesOrders.RequestedDeliveryDate_VDATU ASC ROWS 52 PRECEDING) AS Past52WeekSales,\n FROM\n nn-agc.cortex_test_reporting.SalesOrders SalesOrders\n LEFT JOIN\n nn-agc.cortex_test_reporting.CustomersMD Customers\n ON\n SalesOrders.Client_MANDT=Customers.Client_MANDT\n AND SalesOrders.ShipToPartyItem_KUNNR=Customers.CustomerNumber_KUNNR\n WHERE\n SalesOrders.Client_MANDT= "200" ),\n Forecast AS (\n SELECT\n DemandForecast.CatalogItemID AS Product,\n DemandForecast.StartDateOfWeek AS Date,\n DemandForecast.CustomerId AS Customer,\n DemandForecast.ForecastQuantity AS Sales,\n CustomersMD.City_ORT01 AS Location,\n DemandForecast.ForecastQuantityLowerBound,\n DemandForecast.ForecastQuantityUpperBound\n FROM\n nn-agc.cortex_test_reporting.DemandForecast DemandForecast\n INNER JOIN nn-agc.cortex_test_reporting.CustomersMD CustomersMD\n ON CustomersMD.CustomerNumber_KUNNR=DemandForecast.CustomerId\nand CustomersMD.Client_MANDT= "200"),\n\n DemandPlan AS (\n SELECT\n DemandPlan.MaterialNumber AS Product,\n DemandPlan.WeekStart AS Date,\n DemandPlan.CustomerId AS Customer,\n DemandPlan.DemandPlan AS Sales,\n CustomersMd.City_ORT01 AS Location,\n FROM\n nn-agc.cortex_test_reporting.DemandPlan DemandPlan\n\nINNER JOIN nn-agc.cortex_test_reporting.CustomersMD CustomersMD\n\nON CustomersMD.CustomerNumber_KUNNR=DemandPlan.CustomerId\nand CustomersMD.Client_MANDT= "200" ),\n\n Weather AS(\nSELECT\nWeather.MaxTemp,\nWeather.MinTemp,\nWeather.Country,\nWeather.PostCode,\nWeather.Date,\nWeather.AvgMaxTemp,\nWeather.AvgMinTemp,\nIF\n (COUNT(CASE\n WHEN (MaxTemp>AvgMaxTemp) THEN 1\n END\n ) OVER (PARTITION BY EXTRACT(year FROM Weather.Date),\n EXTRACT(week\n FROM\n Weather.Date),\n Weather.PostCode)>=2,\n TRUE,\n FALSE) AS HeatWave,\n IF\n (COUNT(CASE\n WHEN (MinTemp<AvgMinTemp) THEN 1\n END\n ) OVER (PARTITION BY EXTRACT(year FROM Weather.Date),\n EXTRACT(week\n FROM\n Weather.Date),\n Weather.PostCode)>=2,\n TRUE,\n FALSE) AS ColdFront\nFrom\n(SELECT\n max_temp MaxTemp,\n min_temp MinTemp,\n country Country,\n postcode PostCode,\n date Date,\n AVG(max_temp) OVER(PARTITION BY postcode, extract (week from date) order by extract (week from date) RANGE BETWEEN 20 PRECEDING AND CURRENT ROW) AvgMaxTemp,\n AVG(min_temp) OVER(PARTITION BY postcode, extract (week from date) order by extract (week from date) RANGE BETWEEN 20 PRECEDING AND CURRENT ROW) AvgMinTemp,\nFROM\n nn-agc.cortex_test_reporting.weather_daily) As Weather),\n Trends AS(\n SELECT\n WeekStart,\n Week,\n InterestOverTime,\n Country,\n HierarchyId,\n SearchTerm,\n HistoricalMin,\n HistoricalMax,\n ((InterestOverTime-HistoricalMin)/(HistoricalMax-HistoricalMin))*100 AS NormalizedScore,\n AVG(((InterestOverTime-HistoricalMin)/(HistoricalMax-HistoricalMin))*100) OVER (PARTITION BY Country, HierarchyId )AS AvgNormalizedScore,\n AVG(((InterestOverTime-HistoricalMin)/(HistoricalMax-HistoricalMin))*100) OVER (PARTITION BY Country, HierarchyId ORDER BY Trends.Week RANGE BETWEEN 300 PRECEDING AND CURRENT ROW )AS AvgNormalizedScoreFor10Months,\nFROM (\n SELECT\n WeekStart,\n Extract( WEEK from WeekStart) as Week,\n InterestOverTime,\n CountryCode as Country,\n HierarchyId,\n HierarchyText as SearchTerm,\n MIN(InterestOverTime) OVER (PARTITION BY CountryCode, HierarchyId, EXTRACT(WEEK FROM CAST(WeekStart AS date)) ) AS HistoricalMin,\n MAX(InterestOverTime) OVER (PARTITION BY CountryCode, HierarchyId, EXTRACT(WEEK FROM CAST(WeekStart AS date)) ) AS HistoricalMax\n FROM\n nn-agc.cortex_test_reporting.Trends)Trends\nWHERE\n HistoricalMin != HistoricalMax ),\n Materials as (Select\n MaterialsMD.MaterialText_MAKTX,\n MaterialsMD.MaterialNumber_MATNR,\n MaterialsMD.Client_MANDT,\n ProductHierarchyText.Description_vtext as HierarchyText\n from\n nn-agc.cortex_test_reporting.MaterialsMD MaterialsMD\n left JOIN\n(SELECT\n distinct Hierarchy_Prodh,Client_MANDT,Description_vtext,Level_STUFE,Language_SPRAS\n FROM\n nn-agc.cortex_test_reporting.ProductHierarchiesMD) ProductHierarchyText\n ON left(MaterialsMD.ProductHierarchy_Prdha, 6 ) = ProductHierarchyText.Hierarchy_Prodh\n AND MaterialsMD.Client_MANDT = ProductHierarchyText.Client_MANDT\n AND ProductHierarchyText.Level_STUFE=‘3’\n AND ProductHierarchyText.Language_SPRAS=‘E’\n )\nSELECT\n CalDate.Date,\n CalDate.Week,\n Grid.Product,\n Sales.Client_MANDT,\n materials.MaterialText_MAKTX AS ProductName,\n Grid.CustomerName AS Customer,\n Grid.Location,\n Sales.Country,\n Sales.SalesOrderQuantity AS Sales,\n Sales.Past13WeekSales,\n Sales.Past52WeekSales,\n DemandPlan.Sales DemandPlan,\n ROUND(Forecast.Sales,1) ForecastQuantity,\n ROUND(Forecast.ForecastQuantityLowerBound, 1) AS ForecastQuantityLowerBound,\n ROUND(Forecast.ForecastQuantityUpperBound, 1) AS ForecastQuantityUpperBound,\n Trends.SearchTerm,\n Trends.InterestOverTime,\n ROUND(Weather.MaxTemp,1) AS AverageHighTemperature,\n ROUND(Weather.MinTemp,1) AS AverageLowTemperature,\n ROUND((Weather.MaxTemp+Weather.MinTemp)/2,1) AverageTemperature,\n ROUND(AVG((Weather.MaxTemp+Weather.MinTemp)/2) OVER (PARTITION BY Grid.Location, Grid.Product ORDER BY CalDate.Date ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING ),1) AS MovingAverageTemperature,\n PromotionCalendar.DiscountPercent,\n PromotionCalendar.IsPromo,\n IF(HolidayCalendar.HolidayDate IS NULL,0,1) AS IsHoliday,\n —ForecastOutsideStatisticalRange Impact Score\nIF\n ((DemandPlan.Sales > Forecast.ForecastQuantityUpperBound)\n OR (DemandPlan.Sales < Forecast.ForecastQuantityLowerBound),\n ROUND((ABS(Forecast.Sales-DemandPlan.Sales)/DemandPlan.Sales)100,2),\n 0) AS ForecastOutsideStatisticalRangeImpactScore,\n—HeatWave Impact Score\nIF(HeatWave IS TRUE,\n IF(CorrValue>0.0\n AND (LAG(DemandPlan.sales,1,0)OVER(PARTITION BY Sales.product, Sales.location, EXTRACT(week FROM CalDate.date)\n ORDER BY EXTRACT(week FROM CalDate.date)ASC))>Forecast.ForecastQuantityUpperBound-0.05Forecast.ForecastQuantityUpperBound,\n ROUND((ABS(Forecast.Sales-DemandPlan.Sales)/DemandPlan.Sales)100,2),\n IF(CorrValue<0.0\n And (LAG(DemandPlan.sales,1,0)OVER(PARTITION BY EXTRACT(week FROM CalDate.date)\n ORDER BY EXTRACT(week FROM CalDate.date)ASC))<Forecast.ForecastQuantityLowerBound+0.05Forecast.ForecastQuantityLowerBound,\n ROUND((ABS(Forecast.Sales-DemandPlan.Sales)/DemandPlan.Sales)100,2),0)),0) AS HeatWaveImpactScore,\n\n—ColdFront Impact Score\nIF(ColdFront IS TRUE,\n IF(CorrValue<0.0\n AND (LAG(DemandPlan.sales,1,0)OVER(PARTITION BY Sales.product, Sales.location, EXTRACT(week FROM CalDate.date)\n ORDER BY EXTRACT(week FROM CalDate.date)ASC))>Forecast.ForecastQuantityUpperBound-0.05Forecast.ForecastQuantityUpperBound,\n ROUND((ABS(Forecast.Sales-DemandPlan.Sales)/DemandPlan.Sales)100,2),\n IF(CorrValue>0.0\n And (LAG(DemandPlan.sales,1,0)OVER(PARTITION BY EXTRACT(week FROM CalDate.date)\n ORDER BY EXTRACT(week FROM CalDate.date)ASC))<Forecast.ForecastQuantityLowerBound+0.05Forecast.ForecastQuantityLowerBound,\n ROUND((ABS(Forecast.Sales-DemandPlan.Sales)/DemandPlan.Sales)*100,2),0)),0) AS ColdFrontImpactScore,\n\n — PromoDiffrential Impact Score\nIF\n ( PromotionCalendar.IsPromo=true\n AND ((DemandPlan.Sales-Forecast.Sales)/DemandPlan.Sales) > 0.1,\n ROUND((ABS(Forecast.Sales-DemandPlan.Sales)/DemandPlan.Sales)*100,2),\n 0) AS PromoDiffrentialImpactScore,\n —NonSeasonalTrends Impact Score\nIF\n ((DemandPlan.Sales>Forecast.ForecastQuantityUpperBound\n OR DemandPlan.Sales<Forecast.ForecastQuantityLowerBound)\n AND Trends.Week BETWEEN Trends.Week\n AND Trends.Week+3\n AND ((ABS(NormalizedScore-AvgNormalizedScore)/ AvgNormalizedScore)*100 >0.25),\n ROUND((ABS(NormalizedScore-AvgNormalizedScoreFor10Months)/ AvgNormalizedScoreFor10Months)*100,2),\n 0) AS NonSeasonalTrendsImpactScore,\n\nFROM\n CalDate\nLEFT JOIN\n Grid\nON\n CalDate.date = Grid.date\nLEFT JOIN\n Sales\nON\n CalDate.date = Sales.date\n AND Grid.product = Sales.product\n AND Grid.customer = Sales.customer\n AND Grid.location = Sales.location\nLEFT JOIN\n Forecast\nON\n CalDate.date = Forecast.date\n AND Grid.product = Forecast.product\n AND Grid.customer = Forecast.customer\n AND Grid.location = Forecast.location\nLEFT JOIN\n DemandPlan\nON\n CalDate.date = DemandPlan.date\n AND Grid.product = DemandPlan.product\n AND Grid.customer = DemandPlan.customer\n AND Grid.location = DemandPlan.location\nLEFT JOIN\n Weather\nON\n Grid.PostalCode=Weather.PostCode\n AND CalDate.Date=Weather.Date\nLEFT JOIN\n nn-agc.looker_scratch.LR_8B71C1657801419801_correlation_table_pdt AS CorrelationTable\nON\n Grid.Location=CorrelationTable.Location\n AND Grid.Product=CorrelationTable.product\nLEFT JOIN\n Materials\nON\n Grid.product =Materials.MaterialNumber_MATNR\n AND Materials.Client_MANDT= "200"\n --AND Sales.Client_MANDT=Materials.Client_MANDT\nLEFT JOIN\n Trends\nON\n CalDate.date=Trends.WeekStart\n AND Materials.HierarchyText = Trends.SearchTerm\n AND Sales.Country=Trends.Country\nLEFT JOIN\n nn-agc.cortex_test_reporting.PromotionCalendar PromotionCalendar\nON\n CalDate.date=PromotionCalendar.StartDateOfWeek\n AND Grid.product=PromotionCalendar.CatalogItemId\n AND Grid.customer=PromotionCalendar.Customerid\nLEFT JOIN\n nn-agc.cortex_test_reporting.HolidayCalendar HolidayCalendar\nON\n Grid.Date = HolidayCalendar.HolidayDate)\n AS demand_sensing\nWHERE ((( demand_sensing.Date ) >= ((DATE_ADD(CURRENT_DATE(‘Indian/Christmas’), INTERVAL -89 DAY))) AND ( demand_sensing.Date ) < ((DATE_ADD(DATE_ADD(CURRENT_DATE(‘Indian/Christmas’), INTERVAL -89 DAY), INTERVAL 90 DAY)))))\nGROUP BY\n 1\nORDER BY\n 1 DESC\nLIMIT 500”, “edit_url”: null, “error_pos”: null, “level”: “error”, “sql_error_loc”: {} } ], “drill_menu_build_time”: 0.702209, “has_subtotals”: false }, “dashboard_element_id”: “140ec91abc56d924f17a6d53c5bfb8bd”, “id”: “563ccab0a930b14233446960d214ecec”, “result_source”: “query”, “status”: “error” }