measure: percent_of_market_share_t9 {
type: number
label: “% of Market Share t9”
sql: (${total_trade_value_t9}/nullif(${total_market_trade_value_t9},0)) ;;
value_format_name: percent_2
filters: [current_time_period_boolean : “no”]
}
measure: total_market_trade_value_t9 {
type: number
sql: sum(distinct ${market_trade_value});;
filters: [current_time_period_boolean : “yes”]
}
measure: total_market_trade_value_t99 {
type: sum
sql: ${market_trade_value} ;;
filters: [current_time_period_boolean : “yes”]
}
dimension: market_trade_value {
type: number
sql: ${TABLE}.market_trade_value ;;
}.
This is the lookml query.
Big query code With pd1_data as (
select a.*, from stg-dev-lkh-23bl6.stg_dev_bqd_test_buy_side_pack.pgb14_broker_trade_activity a
where a.your_broker_flag = ‘Yes’
),
current_data as (
select
–trade_date,
broker_name,
– pd1_data.investor_id,
sum(trade_value) as current_trade_value,
/Broker’s Total Trade Value/
sum(trade_volume) as current_trade_volume,
sum(market_trade_value) as current_market_trade_value,
(sum(trade_value)/
(select sum(distinct pd1_data.market_trade_value) from pd1_data
where timestamp(trade_date) between DATE_SUB(TIMESTAMP(‘2025-06-24 00:00:00’), INTERVAL 365 DAY)
AND DATE_SUB(TIMESTAMP(‘2025-06-24 00:00:00’), INTERVAL 1 DAY)
group by all )) current_market_share,
your_total_trade_value,
from pd1_data
inner join (select investor_id,sum(distinct pd1_data.investor_trade_value) Your_Total_Trade_Value from pd1_data
where timestamp(trade_date) between DATE_SUB(TIMESTAMP(‘2025-06-24 00:00:00’), INTERVAL 365 DAY)
AND DATE_SUB(TIMESTAMP(‘2025-06-24 00:00:00’), INTERVAL 1 DAY)
group by investor_id) inv
on pd1_data.investor_id=inv.investor_id
where timestamp(trade_date) between DATE_SUB(TIMESTAMP(‘2025-06-24 00:00:00’), INTERVAL 365 DAY)
AND DATE_SUB(TIMESTAMP(‘2025-06-24 00:00:00’), INTERVAL 1 DAY)
group by all
),
–select * from current_data
–where current_data.investor_id = ‘1088494255’
previous_data as (
select
–trade_date,
broker_name,
pd1_data.investor_id,
– sum(trade_value) as past_trade_value,
– sum(trade_volume) as past_trade_volume,
sum(market_trade_value) as past_market_trade_value,
(sum(trade_value)/
(select sum( distinct pd1_data.market_trade_value) from pd1_data
where timestamp(trade_date) BETWEEN DATE_SUB(TIMESTAMP(‘2025-06-24 00:00:00’), INTERVAL 730 DAY)
AND DATE_SUB(TIMESTAMP(‘2025-06-24 00:00:00’), INTERVAL 366 DAY)
group by all )) past_market_share,
Your_past_Total_Trade_Value,
from pd1_data
inner join (select investor_id,sum(distinct pd1_data.investor_trade_value) Your_past_Total_Trade_Value from pd1_data
where timestamp(trade_date) BETWEEN DATE_SUB(TIMESTAMP(‘2025-06-24 00:00:00’), INTERVAL 730 DAY)
AND DATE_SUB(TIMESTAMP(‘2025-06-24 00:00:00’), INTERVAL 366 DAY)
group by investor_id) inv
on pd1_data.investor_id=inv.investor_id
where timestamp(trade_date) BETWEEN DATE_SUB(TIMESTAMP(‘2025-06-24 00:00:00’), INTERVAL 730 DAY)
AND DATE_SUB(TIMESTAMP(‘2025-06-24 00:00:00’), INTERVAL 366 DAY)
group by all
) ,–select * from previous_data where previous_data.investor_id = ‘1088494255’
–select * from previous_data
main_query as (
select
#trade_date,
#past_trade_date,
current_data.investor_id,
current_data.broker_name,
current_trade_value,
current_trade_volume,
current_market_trade_value,
–total_market_trade_value,
your_total_trade_value,
IfNULL(past_trade_value, 0 )past_trade_value,
IfNULL(past_trade_volume, 0 )past_trade_volume,
IfNULL(past_market_trade_value, 0 )past_market_trade_value,
current_market_share,
past_market_share,
((current_trade_value/your_total_trade_value) * 100) per_of_your_flow,
((past_trade_value/your_past_total_trade_value) * 100) per_past_of_your_flow,
from current_data
LEFT JOIN previous_data
on current_data.investor_id = previous_data.investor_id and current_data.broker_name = previous_data.broker_name
group by all)
SELECT
investor_id , broker_name,
current_trade_value,
current_trade_volume,
current_market_share,
per_of_your_flow,
CASE
WHEN past_market_share = 0 THEN 0
ELSE ((current_market_share - past_market_share) / past_market_share)*100
END AS per_change_market_share ,
CASE
WHEN per_past_of_your_flow = 0 THEN 0
ELSE ((per_of_your_flow - per_past_of_your_flow) / per_past_of_your_flow)*100
END AS per_change_per_of_your_flow
FROM main_query
where investor_id = ‘1088494255’
group by all. This is Big query.
In Big query i am getting values and dashboard different values . Help me out lookml correct code measure:Total_market_trade_value_t9 ,measure:Total_market_trade_value_t99,measure: percentage_of_market_share_t9, dimension: Market_trade_value. Give me correct lookml query which matches to biq query values.
Check above query . suggest correct query which is equal values shows in db and BQ