Calculations in load script

[DevelopCal]

Hello, 

I´d like to increase the performance of my app writing some calculations in my script, but i´m still doing something wrong.

I need an aggegation by month and entity name of Sum( "Paying Customer_12")/sum(Active_Customer_Total)

LIB CONNECT TO xxx
Let vLastYear = Date(AddMonths(Today(),-24),'YYYY-MM-DD');
Load

"Month",
"Paying Customer_12",
Active_Customer_Total,
Sum( "Paying Customer_12")/sum(Active_Customer_Total) as PayingCustomer

group By
"entity_name",
"Month";

LOAD "month" as "Month",
level as level,
entity as Entity,
"age_segment" as Age_Segment,
internal as Internal,
"paying customer" as "Paying Customer",
undefined as Undefined,
warranty as Warranty,
"revenue_total" as revenue_total,
"revenue_total_12" as revenue_total_12,
"paying customer_12" as "Paying Customer_12",
"active_customer_total" as Active_Customer_Total,
"parc_potential_adjusted" as Parc_Potential_Adjusted,
"air filter" as "Air filter",
brakes as Brakes,
bumper as Bumper,
tires as Tires,
windscreen as Windscreen,
"air filter_12" as "Air filter_12",
"brakes_12" as Brakes_12,
"bumper_12" as Bumper_12,
"tires_12" as Tires_12,
"windscreen_12" as Windscreen_12,
throughputs as Throughputs,
"throughputs_12" as Throughputs_12,
"air filter_ac" as "Air filter_ac",
"brakes_ac" as Brakes_ac,
"bumper_ac" as Bumper_ac,
"tires_ac" as Tires_ac,
"windscreen_ac" as Windscreen_ac,
"air filter_12_ac" as "Air filter_12_ac",
"brakes_12_ac" as Brakes_12_ac,
"bumper_12_ac" as Bumper_12_ac,
"tires_12_ac" as Tires_12_ac,
"windscreen_12_ac" as Windscreen_12_ac,
"nps_value" as nps_value,
"nps_count" as nps_count,
"tickets 12of14" as "Tickets 12of14",
"tickets wsv same vp 12of14" as "Tickets WSV Same VP 12of14",
wsvr as wsvr,
bsi_count as bsi_count,
bsi_count_12 as bsi_count_12,
"puk_count" as puk_count,
"puk_12" as puk_12,
"#veh. correct" as "#Veh. correct",
"#veh. incorrect" as "#Veh. incorrect",
"#veh. incorrect proposal" as "#Veh. incorrect proposal",
asr as asr,
outletnumber as Outletnumber,
"owner name" as "Owner name",
region as Region,
city as city,
"group" as "Group",
vehicle_count as vehicle_count,
vehicle_count_12 as vehicle_count_12,
accessories as accessories,
accessories_12 as accessories_12,
"district_name",
"dealer_name",
"outlet_name",
"fourth_level",
"fourth_level_12",
"entity_name";

SQL SELECT "month",
level,
entity,
"age_segment",
internal,
"paying customer",
undefined,
warranty,
"revenue_total",
"revenue_total_12",
"paying customer_12",
"active_customer_total",
"parc_potential_adjusted",
"air filter",
brakes,
bumper,
tires,
windscreen,
"air filter_12",
"brakes_12",
"bumper_12",
"tires_12",
"windscreen_12",
throughputs,
"throughputs_12",
"air filter_ac",
"brakes_ac",
"bumper_ac",
"tires_ac",
"windscreen_ac",
"air filter_12_ac",
"brakes_12_ac",
"bumper_12_ac",
"tires_12_ac",
"windscreen_12_ac",
"nps_value",
"nps_count",
"tickets 12of14",
"tickets wsv same vp 12of14",
wsvr,
"bsi_count",
"bsi_count_12",
"puk_count",
"puk_12",
"#veh. correct",
"#veh. incorrect",
"#veh. incorrect proposal",
asr,
"vehicle_count",
"vehicle_count_12",
accessories,
"accessories_12",
"fourth_level",
"fourth_level_12",
"district_name",
outletnumber,
"owner_id",
"owner name",
region,
city,
"dealer_name",
"outlet_name",
"entity_name",
"group"
FROM AwsDataCatalog."gpm_es_dashboards".main;

[Taoufiq_Zarra · Accepted solution]

@DevelopCal 

what about ?:

LIB CONNECT TO xxx
Let vLastYear = Date(AddMonths(Today(),-24),'YYYY-MM-DD');


Input:

LOAD "month" as "Month",
level as level,
entity as Entity,
"age_segment" as Age_Segment,
internal as Internal,
"paying customer" as "Paying Customer",
undefined as Undefined,
warranty as Warranty,
"revenue_total" as revenue_total,
"revenue_total_12" as revenue_total_12,
"paying customer_12" as "Paying Customer_12",
"active_customer_total" as Active_Customer_Total,
"parc_potential_adjusted" as Parc_Potential_Adjusted,
"air filter" as "Air filter",
brakes as Brakes,
bumper as Bumper,
tires as Tires,
windscreen as Windscreen,
"air filter_12" as "Air filter_12",
"brakes_12" as Brakes_12,
"bumper_12" as Bumper_12,
"tires_12" as Tires_12,
"windscreen_12" as Windscreen_12,
throughputs as Throughputs,
"throughputs_12" as Throughputs_12,
"air filter_ac" as "Air filter_ac",
"brakes_ac" as Brakes_ac,
"bumper_ac" as Bumper_ac,
"tires_ac" as Tires_ac,
"windscreen_ac" as Windscreen_ac,
"air filter_12_ac" as "Air filter_12_ac",
"brakes_12_ac" as Brakes_12_ac,
"bumper_12_ac" as Bumper_12_ac,
"tires_12_ac" as Tires_12_ac,
"windscreen_12_ac" as Windscreen_12_ac,
"nps_value" as nps_value,
"nps_count" as nps_count,
"tickets 12of14" as "Tickets 12of14",
"tickets wsv same vp 12of14" as "Tickets WSV Same VP 12of14",
wsvr as wsvr,
bsi_count as bsi_count,
bsi_count_12 as bsi_count_12,
"puk_count" as puk_count,
"puk_12" as puk_12,
"#veh. correct" as "#Veh. correct",
"#veh. incorrect" as "#Veh. incorrect",
"#veh. incorrect proposal" as "#Veh. incorrect proposal",
asr as asr,
outletnumber as Outletnumber,
"owner name" as "Owner name",
region as Region,
city as city,
"group" as "Group",
vehicle_count as vehicle_count,
vehicle_count_12 as vehicle_count_12,
accessories as accessories,
accessories_12 as accessories_12,
"district_name",
"dealer_name",
"outlet_name",
"fourth_level",
"fourth_level_12",
"entity_name";

SQL SELECT "month",
level,
entity,
"age_segment",
internal,
"paying customer",
undefined,
warranty,
"revenue_total",
"revenue_total_12",
"paying customer_12",
"active_customer_total",
"parc_potential_adjusted",
"air filter",
brakes,
bumper,
tires,
windscreen,
"air filter_12",
"brakes_12",
"bumper_12",
"tires_12",
"windscreen_12",
throughputs,
"throughputs_12",
"air filter_ac",
"brakes_ac",
"bumper_ac",
"tires_ac",
"windscreen_ac",
"air filter_12_ac",
"brakes_12_ac",
"bumper_12_ac",
"tires_12_ac",
"windscreen_12_ac",
"nps_value",
"nps_count",
"tickets 12of14",
"tickets wsv same vp 12of14",
wsvr,
"bsi_count",
"bsi_count_12",
"puk_count",
"puk_12",
"#veh. correct",
"#veh. incorrect",
"#veh. incorrect proposal",
asr,
"vehicle_count",
"vehicle_count_12",
accessories,
"accessories_12",
"fourth_level",
"fourth_level_12",
"district_name",
outletnumber,
"owner_id",
"owner name",
region,
city,
"dealer_name",
"outlet_name",
"entity_name",
"group"
FROM AwsDataCatalog."gpm_es_dashboards".main;

left join Load

"Month",
"entity_name",
Sum( "Paying Customer_12")/sum(Active_Customer_Total) as PayingCustomer
resident Input group By "entity_name","Month";

[DevelopCal]

 Invalid expression but don´t know where 

thanks taoufiq!

[DevelopCal]

@Taoufiq_Zarra  could you please help me again? thanks in advance!!!

[Kushal_Chawda · Accepted solution]

@DevelopCal  just change the below part of the script. Remove "Paying Customer_12", Active_Customer_Total from load as you are already grouping on these field and add "entity_name" in load as you are doing grouping on that in group by clause

Load

"Month",

"entity_name",
Sum( "Paying Customer_12")/sum(Active_Customer_Total) as PayingCustomer

group By
"entity_name",
"Month";

[Taoufiq_Zarra]

typos error, try : @DevelopCal 

LIB CONNECT TO xxx
Let vLastYear = Date(AddMonths(Today(),-24),'YYYY-MM-DD');


Input:

LOAD "month" as "Month",
level as level,
entity as Entity,
"age_segment" as Age_Segment,
internal as Internal,
"paying customer" as "Paying Customer",
undefined as Undefined,
warranty as Warranty,
"revenue_total" as revenue_total,
"revenue_total_12" as revenue_total_12,
"paying customer_12" as "Paying Customer_12",
"active_customer_total" as Active_Customer_Total,
"parc_potential_adjusted" as Parc_Potential_Adjusted,
"air filter" as "Air filter",
brakes as Brakes,
bumper as Bumper,
tires as Tires,
windscreen as Windscreen,
"air filter_12" as "Air filter_12",
"brakes_12" as Brakes_12,
"bumper_12" as Bumper_12,
"tires_12" as Tires_12,
"windscreen_12" as Windscreen_12,
throughputs as Throughputs,
"throughputs_12" as Throughputs_12,
"air filter_ac" as "Air filter_ac",
"brakes_ac" as Brakes_ac,
"bumper_ac" as Bumper_ac,
"tires_ac" as Tires_ac,
"windscreen_ac" as Windscreen_ac,
"air filter_12_ac" as "Air filter_12_ac",
"brakes_12_ac" as Brakes_12_ac,
"bumper_12_ac" as Bumper_12_ac,
"tires_12_ac" as Tires_12_ac,
"windscreen_12_ac" as Windscreen_12_ac,
"nps_value" as nps_value,
"nps_count" as nps_count,
"tickets 12of14" as "Tickets 12of14",
"tickets wsv same vp 12of14" as "Tickets WSV Same VP 12of14",
wsvr as wsvr,
bsi_count as bsi_count,
bsi_count_12 as bsi_count_12,
"puk_count" as puk_count,
"puk_12" as puk_12,
"#veh. correct" as "#Veh. correct",
"#veh. incorrect" as "#Veh. incorrect",
"#veh. incorrect proposal" as "#Veh. incorrect proposal",
asr as asr,
outletnumber as Outletnumber,
"owner name" as "Owner name",
region as Region,
city as city,
"group" as "Group",
vehicle_count as vehicle_count,
vehicle_count_12 as vehicle_count_12,
accessories as accessories,
accessories_12 as accessories_12,
"district_name",
"dealer_name",
"outlet_name",
"fourth_level",
"fourth_level_12",
"entity_name";

SQL SELECT "month",
level,
entity,
"age_segment",
internal,
"paying customer",
undefined,
warranty,
"revenue_total",
"revenue_total_12",
"paying customer_12",
"active_customer_total",
"parc_potential_adjusted",
"air filter",
brakes,
bumper,
tires,
windscreen,
"air filter_12",
"brakes_12",
"bumper_12",
"tires_12",
"windscreen_12",
throughputs,
"throughputs_12",
"air filter_ac",
"brakes_ac",
"bumper_ac",
"tires_ac",
"windscreen_ac",
"air filter_12_ac",
"brakes_12_ac",
"bumper_12_ac",
"tires_12_ac",
"windscreen_12_ac",
"nps_value",
"nps_count",
"tickets 12of14",
"tickets wsv same vp 12of14",
wsvr,
"bsi_count",
"bsi_count_12",
"puk_count",
"puk_12",
"#veh. correct",
"#veh. incorrect",
"#veh. incorrect proposal",
asr,
"vehicle_count",
"vehicle_count_12",
accessories,
"accessories_12",
"fourth_level",
"fourth_level_12",
"district_name",
outletnumber,
"owner_id",
"owner name",
region,
city,
"dealer_name",
"outlet_name",
"entity_name",
"group"
FROM AwsDataCatalog."gpm_es_dashboards".main;

left join Load

"Month",
"entity_name",
Sum( "Paying Customer_12")/sum(Active_Customer_Total) as PayingCustomer
resident Input group By "entity_name","Month";