Hi,
I have this issue for a while, i need to make a heatmap using a map graphic where i have points in each country where I have the "Sales Factor" for that particular country.
I have filters : Country, Make, Model, Fuel Type, Gearbox. I need to be able to select any combination of filters and get the result on the map correctly.
This is my expression for the calculation of "Sales Factor" (This is (online cars/sold cars)*30):
=floor(Count(distinct
{<Date={">=$(=date('2023-01-01'))<=$(=date(today()))"},duplicateStatus={0}>}
[_id])
/
Count(
distinct
{<
Date={">=$(=date('2023-01-01'))<=$(=date(today()))"},
country.sold={'$(=concat(distinct country,''','''))'},
[make.sold]={'$(=concat(distinct make,''','''))'},
[model.sold]={'$(=concat(distinct model,''','''))'},
[fuelType.sold]={'$(=concat(distinct fuelType,''','''))'},
[gearbox.sold]={'$(=concat(distinct gearbox,''','''))'}
>}
[_id.sold])
*30)
My script is:
TRACE
*** CONSUMPTION ***;
Search Include
Month_Year,
Date,
dealer,
country,
dealerAddress,
dealerPhone,
vehicleStatus,
color,
make,
manufactureYear,
mileage,
model,
LCV,
trimNorm,
fuelType,
subModel,
variant,
"Contact Person",
KAM,
Email,
"Website Link",
"Phone 2",
"Match Status",
dealer_existing,
dealer_newly_scraped,
valid_dealer,
KAM_class,
Month_Year_lastScrape,
Month_Year_firstScrape;
Qualify * ;
TRACE *** Loading masterCarData ***;
masterCarData:
LOAD distinct
fuelType,
gearbox
FROM [$(vTransform)/Transformed_masterCarData.qvd]
(qvd)
;
Unqualify *;
TRACE *** Loading Main Table ***;
Data:
LOAD
AutoNumberHash128(_id) as _id, //short _id
website,
adType,
dealerHash,
dealer,
country,
dealerAddress,
dealerPhone,
"url",
baseCurrency,
Date(floor(firstScrape)) as firstScrape,
Date(floor(lastScrape)) as lastScrape,
firstScrape&'-'&lastScrape as first_last,
daysOnline,
vehicleStatus,
adChange,
variantNorm,
trimNorm,
subModelNorm,
generationNorm,
chassis,
co2,
Weeks,
"color",
currentPrice,
// currentPriceExVAT,
enginePower,
engineVolume,
firstRegDate,
fuelType,
gearbox,
drivingWheel,
lower(make) as make,
manufactureYear,
mileage,
lower(model) as model,
LCV,
subModel,
variant,
vin,
status,
monthlySoldStd,
salesDaysStd,
mileageCorrectionStd,
salesFactorStd,
duplicateStatus, //FALSE is for not duplicates
// modifiedAt,
"Contact Person",
KAM,
Email,
"Website Link",
"Phone 2",
"Match Status",
dealer_existing,
dealer_newly_scraped,
valid_dealer,
KAM_class,
Month_Year_lastScrape,
Currency_Rate,
currentPrice_EUR,
Month_Year_firstScrape
FROM [$(vTransform)/Transformed_carDataExtended.qvd]
(qvd)
where
Date(floor(firstScrape))>=(date(today())-220) and
Exists(masterCarData.gearbox,gearbox) and Exists([masterCarData.fuelType],fuelType)
and not wildmatch(make,'=D*','stell\\*')
// and wildmatch(country,'PT','ES') //Add filters in the Load section
;
TRACE -- Main Table Loaded --;
Drop table masterCarData;
//----------------------------------------------------------------------------------------------------
/*
Additional table to calculate the Sales Factor
This table is linked to the Master Calendar.
We Link the lastScrape Date to the Calendar Date
*/
Factor_Table:
Load
Date(lastScrape) as Date,
_id as _id.sold,
make as make.sold,
model as model.sold,
country as country.sold,
subModel as subModel.sold,
variantNorm as variantNorm.sold,
gearbox as gearbox.sold,
fuelType as fuelType.sold,
dealer as dealer.sold,
trimNorm as trimNorm.sold,
mileage as mileage.sold,
currentPrice_EUR as currentPrice_EUR.sold
Resident Data
where vehicleStatus<>0 and duplicateStatus=0;
I have other parts to the script like Master Calendar , Mapping and Transformation that I think are not the issue.
I need to have this values displayed in the country inicially: For example DE(Germany): "233" is the current sales factor for Germany.
My map shows:
When it not with any filters on is:
