Fill date gaps

Follow

Xavier

• January 20, 2016 15:27
• Edited

Hi,

I'm new to Datameer and having a little trouble figuring out how to do something.

Say I start a workbook with a list of purchased items with item name and date of purchase.

In a new sheet I make a GROUPBY on the date in the first column and a GROUPCOUNT in the second column to have the count of purchased items per day.

So now I have a row for each day on which there was a purchase, what I need to do is to also have rows for days on which there was no purchase (with a 0 in the second column).

How would you do that ?

Thanks

 

0

• 

  Frances Angulo

  • January 20, 2016 15:29

  You can use the "explode range" function to create a data set of date sequences and then use that output as an outer join to actual set. You can then use the "denullify" function to replace the non matches with zeros. 

  Give it s try! 

  1

  Comment actions Permalink
• 

  Xavier

  • January 20, 2016 16:15

  Thanks Frances, following your insight I've found the EXPAND_DATE_RANGE that seems to do exactly what I wanted.

  0

  Comment actions Permalink
• 

  Frances Angulo

  • January 20, 2016 16:16
  • Edited

  Ah yes! My suggestion would have required a formatting of a date. Well done! 

  0

  Comment actions Permalink

Please sign in to leave a comment.