This Klip counts the number of open tickets by date over a recurring last seven days. In this example, it is assumed that today is August 31, 2016. To ensure the count is correctly aligned to the date, LOOKUP is used.
This Klip uses the following data source:
Step 1: Building the X Axis of a Bar/Line Chart component
In this example, the X Axis is set with dates that range from seven days before today's date to see how many tickets were opened on each day of the current week. DATERANGE is used to return the dates and DATE_ADD to specify the unit and time period from TODAY. Since date functions return in Unix time format, DATEVALUE is wrapped around the entire formula so the dates return in the same format ("M/d/yy") as the data source.
DATEVALUE(DATERANGE(DATE_ADD(TODAY(), day, -6),TODAY()),"M/d/yy")
Step 2: Building the Series of a Bar/Line Chart component
LOOKUP is used to correctly align the data with the X Axis dates using an X Axis formula reference. Without using LOOKUP, the data would return chronologically ordered but misaligned, for example August 26, 2016 would inaccurately show a count of open tickets.
GROUP and COUNTDISTINCT are used to group the dates and count the times they are recorded to return the amount of open tickets per day.
LOOKUP(!X Axis Dates ,GROUP(values),COUNTDISTINCT(values))