return today value even when the query returns null

[CESAR DALDOSSO]

Hello, I was calculating a trhoughput ratio, that basically i want to calculate on a weekly basis the total of tickets delivered by the number of developers of a squad. So if the number of tickets delivered on a week is 5 and the number of developers is 5, then the ratio is 1.

However, I cannot include weeks where no issues were delivered.

EG

week 1 - 5 tickets -> ratio 1 (5/5)

week 2 - 0 tickets -> ratio 0.5 (5/10)

week 3 - 10 tickets -> ratio 1 (15/15)

instead of doing like this, the ratio calculates as the following

week 1 - 5 tickets -> ratio 1 (5/5)

week 3 - 10 tickets -> ratio 1,5 (15/10)

this happens because when Analytics does not fill with zero empty weeks.

To fix this, I tried the following ->

1. Get today date (eg, if today is January 20th, the day is 20)

2. Do calculations to check the number of business days incurred

3. retrieve on a query the total number of tickets delivered since jan 1st

4.calculate the ratio based on number of tickets delivered divided by number of devs, then get this result and divide by number of weekdays.

to get today date, I am currently using a query that pulls any ticket from any project that was created today. However, there are days where no issues were created.

So I have two questions

1. is it possible to create the "today date" on a query ? 

2. is it possible to replace all nulls values from the previous query and really include a zero , so I would have a result like the following? Os any other idea ?

week 1 |
week 2  |0

week 3 | 10 

[Tracy Chow]

Hi Cesar,

1) To get today's date, you can use the {TODAY} relative date variable.

2) The "Zero fill" step should accomplish this. Take a look at the example here: https://support.atlassian.com/analytics/docs/visual-sql-steps/#Zero-fill 

Let me know if these work for you!