AnsweredAssumed Answered

Need to return 12 months for a graph even if no results for that month

Question asked by madmike6537 on Apr 26, 2015
Latest reply on May 11, 2015 by erolst

Hi,

 

I am using SQL for a delimited data graph, and I have a problem. I have a month that doesnt have any data, so its causing my numbers to all be off by one month since that month is blank.

 

I need a way to make sure every month is accounted for even if there are no numbers that month. Anyone have an idea how to do that?

 

Here is the SQL for my chart( this is for the prior year's data):

 

Let([

 

revenue = ExecuteSQL ( "SELECT SUM(Line_Item_Total) FROM CUSTOMER WHERE DateCreated >= ? AND DateCreated <= ? GROUP BY SetupFilter ORDER BY SetupFilter ASC"; ""; "";

 

Date(1;1; Year(Get(CurrentDate)) - 1);

 

Date(12;31; Year(Get(CurrentDate)) -1)

 

)];

 

revenue

 

)

Outcomes