Grouping multiple dates from json in klipfolio

Question

I have a JSON formatted input in the below form:

[
  {
    "hash":"abcdefg",
    "Stage 1 Status":"Complete",
    "Stage 1 Completion":"2021-01-16T19:56:10+02:00",
    "Stage 2 Status":"Complete",
    "Stage 2 Completion":"2021-02-17T16:30:30+03:00",
    "Stage 3 Status":"Complete",
    "Stage 3 Completion":"2021-03-17T16:30:34+03:00"
  },
  {
    "hash":"klmnop",
    "Stage 1 Status":"Complete",
    "Stage 1 Completion":"2021-01-16T19:56:10+02:00",
    "Stage 2 Status":"Open",
    "Stage 2 Completion":"2021-02-17T16:30:34+03:00"
  },
  {
    "hash":"jklmn",
    "Stage 1 Status":"Complete",
    "Stage 1 Completion":"2021-01-16T19:56:10+02:00",
    "Stage 2 Status":"Lost",
    "Stage 2 Completion":"2021-07-17T16:30:30+03:00"
  }
]

And I want to make a klip in klifolio, Showing me the Completed stages of each month like the below output derived from the above data.

	January	February	March
Stage1	3	0	0
Stage2	0	1	0
Stage3	0	0	1

The data is of three years and I need to show the years counts in separate tables. New to klipfolio so till now I have constructed arrays for table column titles and row tags, but I am having difficulty with the query.

Answer 1

To accomplish this in Klipfolio, you'll need to first organize your JSON data into columnar structures so you can perform queries on it. To do this you need one set of data for the status, completion and stage name.

1. For the stage name, you can utilize kf:names and the LEFT() function. This will look like:

   LEFT(@kf:names(/,FALSE),7)

By default, kf:names returns data in alphabetical order, and the FALSE parameter return the data in the order from top to bottom of the JSON structure.

LEFT() will return the 7 leftmost characters, which will only return "Stage 1" instead of "Stage 1 Status"

2. To return the date, the wildcard selector is needed as well as the contains() function to return any values for the fields that have the name the contains "Completion".

   @/*[contains(name(),'Completion')]

3. Finally, to return the status you'll need to do the same as above, but search for field names that contain "status".

   @/*[contains(name(),'Status')]

4. From here you should have 3 arrays of data you can query upon. The first query which returns all field names in your JSON structure will first need to be filtered to return the same amount of items as the dates and status arrays. You can use SELECT() to only return fields with 'Completion' or 'Status' as we know there is a one-to-one relationship between these fields and the values.

SELECT(LEFT(@kf:names(/,FALSE),7),CONTAINS(@kf:names(/,FALSE),"Status"))

This will return 7 items to match the 7 items of dates and statuses.

Now that the data is in the proper shape, a LOOKUP() can be used in each column to align the count of completed records per stage, by using the SELECT(), AND(), GROUP() and COUNTDISTINCT() functions.

First, results reference the column with the stage names, in the first parameter of LOOKUP(). Then use a SELECT() to filter for only the completed stages which fall in a specific month. For the dates, you'll need to convert to unix time with DATE() and then DATEVALUE() to convert to something that is rolled up to a month like "yyyyMM" format which would return "202101" if the date is in January of this year.

The SELECT() looks like this:

SELECT(SELECT(LEFT(@kf:names(/,FALSE),7),CONTAINS(@kf:names(/,FALSE),"Status")),AND(DATEVALUE(DATE(@/*[contains(name(),'Completion')],"yyyy-MM-dd"),"yyyyMM")="202101",@/*[contains(name(),'Status')]="Complete"))

In the second parameter wrap a GROUP() around the SELECT() to group like values and in the third parameter of the LOOKUP() wrap the SELECT() with a COUNTDISTINCT() to count the items per group. The whole formula for the January 2021 column would look like so:

LOOKUP(&Stages,GROUP(SELECT( SELECT(LEFT(@kf:names(/,FALSE),7),CONTAINS(@kf:names(/,FALSE),"Status")), AND(DATEVALUE(DATE(@/*[contains(name(),'Completion')],"yyyy-MM-dd"),"yyyyMM")="202101", @/*[contains(name(),'Status')]="Complete"))), COUNTDISTINCT(SELECT( SELECT(LEFT(@kf:names(/,FALSE),7),CONTAINS(@kf:names(/,FALSE),"Status")), AND(DATEVALUE(DATE(@/*[contains(name(),'Completion')],"yyyy-MM-dd"),"yyyyMM")="202101", @/*[contains(name(),'Status')]="Complete"))))

From there, you can change the 202101 in the formula to any yyyyMM to return a particular month data, IE. for the next column, February it would be 202102.