I have a table which receives fraudulent transaction entries on weekdays. I would like to calculate weekly average number of transaction entries and so it will be [ count of rows per week / 5 ].
Is there a simple way to achieve this instead of writing native query?
Hi @rahul168
If either of these doesn't work for you, then I guess it's SQL.
Using a Custom Expression: Custom expressions in the notebook editor
Or adding a column after the aggregation: Redirecting…
Thanks @flamber
How does it work by default for weekly average does it always divide by 7 or counts the available dates in the data? Also how do you calculate for current week which may not be complete?
I am bit concerned with this solution as it may help calculate with fixed number like 5 (days) but what if there are holidays and we have data for only 4 days. Also the users can change from weekly interval to monthly or some other time window.
@rahul168 You're asking for way too much complexity, which would likely not be possible in the GUI, so you should do it in SQL.
Maybe this is already working with default functionality in the Metabase. Can you please help me with following or point me to some relevant document?
How does weekly average work by default - does it always divide by 7 or counts the available dates in the data? Also how do you calculate for current week which may not be complete?
@rahul168 When you group by Week on a date column, it will create granularity of a week - based on the start of week, which you have configured in Admin > Settings > Localization.
Current incomplete week is just seen as a week, like the rest.
You cannot get exactly what you want in the GUI, you'll need to do SQL.
