Request data only 2 years ago

Get Help
1

Hi,

I want to request data and filter it on a date field to only keep data from 2 years ago without writing 2020 because in 2023 it will be for 2021.

Once i get my data i want to sum each month and show it in a linear graph by month like if this is like that in my db :
January -> 50k €
February -> 25 k€
Metabase should show it :
January -> 50 k€
February -> 75 k€

If I use the filter "Last 2 years" it will take 2020 and 2021, is there a solution without SQL to only take 2020 ?
I tried in SQL but the sum explained before didn't work :

SELECT 
    str_to_date(concat(date_format(`llx_propal`.`datep`, '%Y-%m'), '-01'), '%Y-%m-%d') AS `datep`, 
    `Origine - fk_input_reason`.`label` AS `Origine - fk_input_reason__label`, 
    sum(`llx_propal`.`total_ht`) AS `sum`
FROM 
    `llx_propal`
LEFT JOIN `llx_societe` `Tiers - Client` ON `llx_propal`.`fk_soc` = `Tiers - Client`.`rowid` 
LEFT JOIN `llx_categorie_societe` `Catégories - tiers` ON `Tiers - Client`.`rowid` = `Catégories - tiers`.`fk_soc` 
LEFT JOIN `llx_categorie` `Catégories - ID catégorie` ON `Catégories - tiers`.`fk_categorie` = `Catégories - ID catégorie`.`rowid` 
LEFT JOIN `llx_c_input_reason` `Origine - fk_input_reason` ON `llx_propal`.`fk_input_reason` = `Origine - fk_input_reason`.`rowid` 
LEFT JOIN `llx_entity` `llx_entity__via__entity` ON `llx_propal`.`entity` = `llx_entity__via__entity`.`rowid`
WHERE ((`llx_propal`.`fk_statut` = 1
    OR `llx_propal`.`fk_statut` = 2 OR `llx_propal`.`fk_statut` = 3 OR `llx_propal`.`fk_statut` = 4)
    AND `Catégories - ID catégorie`.`label` = 'XXX' 
    AND `llx_entity__via__entity`.`label` = 'XXX' 
    AND `llx_propal`.`datep` >= makedate(year(date_add(now(6), INTERVAL -2 year)), 1) 
    AND `llx_propal`.`datep` < makedate(year(date_add(now(6), INTERVAL -1 year)), 1) 
    AND `Origine - fk_input_reason`.`label` = 'AO')
GROUP BY month(`llx_propal`.`datep`), `Origine - fk_input_reason`.`label`
ORDER BY month(`llx_propal`.`datep`) ASC, `Origine - fk_input_reason`.`label` ASC

Thanks for your help

2

Hi @erwan_chtlp
Yes, you can use the "Starting from":
https://www.metabase.com/docs/latest/questions/query-builder/introduction#filtering

3

Hi @flamber
I don't have the same window :confused: I'm using Metabase 0.42.2 is this fonctionnality in the latest updates ?

4

@erwan_chtlp Yes, that was added in newer versions (think it was v44).

1 Like
5

Thank you I will make the update ! :slight_smile: