Solved

Issue plotting RUNSUM without data at regular intervals

  • 20 March 2023
  • 3 replies
  • 69 views

Hello,

I’m attempting to plot fees + expenses to date by week to show project expenditure against budget. I’m using the following metric definition to create this specific trend. 

SELECT RUNSUM(IFNULL(Time Entry: Fees In Cents/Currency: Base Unit, 0)) + RUNSUM(IFNULL(Expense: Amount In Cents/Currency: Base Unit, 0)) with PF except Date (Shared)

This works as expected when there is data every week, however not all projects will have expenses each week. When there is intermittent data, RUNSUM doesn’t graph correctly. Essentially there are holes in the graph, and as soon as there is data again the graph will continue, but leave a gap. This issue is not corrected by the “ifnull” statement in the metric definition.

 

For example, the yellow trend below is fees + expenses to date, the green trend is fees to date. Fees to date is shown correctly because there are fees already entered for the week of Sun 3/19/2023, but there are no expenses yet so the yellow trend line stops at the previous week. I would expect RUNSUM to treat this as a “0” value and graph up to 3/19 like the fees to date trend. 

 

I’m wondering if there is a way to force RUNSUM to see missing data (regardless of the time interval that is being plotted) as an actual “0” value so it will plot correctly even if there isn’t real data entered. Entering $0 expenses is not a viable solution because this would have to be done for many projects each week where there happens to be no normal expenses. 

 

Any assistance is appreciated, thanks

 

icon

Best answer by Moises Morales 21 March 2023, 00:45

View original

3 replies

Userlevel 3

Hi Logan, 

The IFNULL function will only return a “0” for any “null values”. It will not return a value when there is no data. I can also suggest double checking our documentation here: Null column | The GoodData Community. I’m not aware of any workaround that could bypass this, if the null values do not exist in your database, then the ifnull keyword won’t be able to make the 0's appear. You could make it work by inserting the null values in your source data.

 

-Moises

Hi @Moises Morales

Thanks for the response. Is there any function to check if there is an empty/missing value? If I could at least determine that data is missing more easily I might be able to easily to add null/zero values. It still comes down to the missing data is across many sources so even knowing where there are gaps is difficult to track down, unless I happen to have it graphed and can see the gap. 

Userlevel 3

Hi Logan, 

This would need to be determined in your source data. I believe something like this might help: How to get missing values in sql? - Stack Overflow

 

-Moises

Reply