Hello, we have created a chart that shows % of failed payments by decline code over all failed payments in a week. The original chart was weekly using the following calculation

SELECT (SELECT {metric/payments})  / (SELECT {metric/payments} BY {label/CREATED_AT.week} ALL OTHER)

We have developed a granularity toggle plugin which allows users to select if they want to view charts daily, weekly or monthly. We would like to use it on this chart as well, but if users selects to display it in different granularity then weekly the above calculation is not going to be correct. Is there a better way how to calculate this metric without specifying the week attribute?

Hello Zuzana, Thank you for reaching out to us. I would like to clarify what you mean by "the above calculation is not going to be correct." Could you please explain the difference between this result and the expected one? I will test it on my end and provide you with an update. Thank you.

Hi, if we change the date granularity to day instead of week the calculation would remain

# payments with result category / # payments in the week of the date

but the expectation is

# payments with result category / # payments on the day

Hi Zuzana, Thank you for your patience. I think I achieved what you wanted after testing my environment with sample/dummy data. Your query was almost correct, but when using the BY clause, we always need to ensure that the granularity is set to the minimum level we desire (see https://www.gooddata.com/docs/cloud/create-metrics/maql/filters/overriding-attribute-filters/by/ for reference). If you keep the same query but change ``.week`` to ``.day``, it will work. In the attached pictures, you can see the results I've obtained. For the scatter chart, the situation is similar; however, if there are identical percentages for multiple categories, only the 'top' dot will be displayed. Please, let me know if it works for you.

That is amazing, thank you very much! I will try it 🙂

Maybe you can try

BY ALL {label/risk_category}

it will compute it for all categories together but it will still let breakdown of the metric by attribute used on X-Axis of the chart

Hi Zuzana, I would like to know if you tried or not the solutions previously proposed by me and Jakub? ** Thank you Jakub

Hi, we have implemented your solution. I haven't tried the one of Jakub because we prefer to have the solution more generic, i.e. the solution with the day gives correct results and can be used for different breakdowns. Thanks again for your support!

Hi Zuzana, I'm glad the issue has been resolved 🙂. Please don't hesitate to contact us if you need anything else. Have an amazing rest of the week! 🙂