8 Replies Latest reply on May 11, 2015 2:59 PM by erolst

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

    madmike6537

      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

       

      )