Aggregation problem (1 field reacts to 2)

[Nolgath]

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: