I have two tables - Data Logs (Impressions, LAYOUT, AREA) and Flights (ID, FlatRate, StartDate, EndDate). They are connected through the primary key. I am trying to calculate Revenue, whose calculation uses FlatRate, StartDate, EndDate, Impressions calculated via ID without a primary filter. I managed to achieve specific numbers, but I do not know how to get rid of the dependency on ID in the table in the picture. I would like to display only the sum. However, when I remove ID, I get an error, attached picture. Does anyone know how to do it?

Hello Markéta, thank you for reaching out! In order to assist you better and make the forum accessible to a wider audience, could you please post your question in English? This ensures that the information can be helpful not only to you but also to others who may encounter a similar issue in the future. Thank you for your understanding!

I am not sure how exactly the tables are connected. Is the ID column stored in both tables? Is it the primary key in both tables? If not, in which table is it the PK?

Tables are connected M to N and all through the primary key, which is ID, Data Logs has Flight Creative ID, Flight Creatives has its own ID and Flights ID and Flights has its own ID.

@Moises Morales sorry man, we will continue in Czech language 😉 @Jakub Sterba mohl bys Markete pomoci, prosim?

@Jakub Sterba We can continue in English, I just always forget, sorry 😅

No problem, we are fluent in Czech 😉

My feeling is that there needs to be done some trick in metrics to calculate correctly, something like for each, or something to be able to slide by layout and do correctly sum of calculations on BG in GoodData. But I am not sure. there is several calculations on BG.

M:N is complicated, I am not an expert in this area. @Jakub Sterba or @Vojtech Tollar are experts.

Hi, I've had a look, and I tried to create a reproducer on my end. The problem is that the engine is unable to compute the aggregation inside the metric, because there is not path from [start_date, end_date] to [area, layout]. I assume that your metric looks something like this.

SELECT SUM(
    DATETIME_DIFF({label/END_DATE.minute}, {label/START_DATE.minute})
    ) * {fact/flatrate}

On my end, all I needed to do was to move the flat rate multiplication inside the sum. This makes it so that the engine "lifts" the datetime_diff expression onto the dataset and it can then figure out how to do the aggregation.

SELECT SUM(
    DATETIME_DIFF({label/END_DATE.minute}, {label/START_DATE.minute})
    * {fact/flatrate})

Please correct me if any of my assumptions was incorrect 🙂

DateDiff mam napsán takto: SELECT SUM(DATETIME_DIFF({label/startDate2.day}, IF {label/endDate2.day} != NULL THEN {label/endDate2.day} ELSE THIS(DAY) END, DAY) + 1), a metriku date_diff nevidím.

Aha rozumím, jelikož tam není fakt tak stejný efekt se dá získat pomocí BY pravidla.

SUM(SELECT DATETIME_DIFF({label/startDate2.day},
IF {label/endDate2.day} != NULL
THEN {label/endDate2.day}
ELSE THIS(DAY) END, DAY) + 1 BY {dataset/flights})

No teď mi to nevychází početně, určitě je to cesta správným směrem. Moje metriky pro výpočet revenue: flight_days_diff: SELECT SUM(DATETIME_DIFF({label/startDate2.day}, IF {label/endDate2.day} != NULL THEN {label/endDate2.day} ELSE THIS(DAY) END, DAY) + 1 ) BY {dataset/flights}, Flight_days_now: SELECT SUM(SELECT CASE WHEN THIS(DAY,1) < {label/startDate2.day} THEN 0, WHEN THIS(DAY,1) BETWEEN {label/startDate2.day} AND {label/endDate2.day} THEN DATETIME_DIFF({label/startDate2.day}, THIS(DAY,1), DAY) ELSE {metric/flight_days_diff_test} END) BY {dataset/flights}, sum_impression_per_flight: SELECT SUM({fact/data_logs.impressions}) BY {label/flights.id} WITHOUT PARENT FILTER, cpm: SELECT SUM(IF {fact/costImpressions} > 0 THEN {fact/costImpressions} ELSE {fact/flatRate} * {metric/flight_days_now_test}/{metric/flight_days_diff_test}/{metric/impressions_flight} *1000 END) revenue: SELECT SUM({fact/data_logs.impressions}) *{metric/cpm_transfer}/ 1000