I have a issue I have this script :
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;
For sold cars I have factor_table and for online I use the normal fields id country make etc..
I have made a map and I need to place this measure : (Sales Factor)
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
I have make.sold country.sold _id.sold etc.. and id make country etc..
I need that measure to work on the map labels (to show the values on each country)
It works, but only when I filter a country it gives me the right value, but If i have the whole map view with no filters I get really wrong values. I have even tried to aggr in country, and country,country.sold none of them do the job.
Do you guys have any clues on this?
ps: example in image, germany should be 147 for example.
