Filling in month gap

[LaRetta]

Hi everyone,

I have a simple chart which displays month's totals but some months have no data.  I'm trying to adjust the script to set the 'empty months' in the date range to 0.  Does anyone know if that is possible?  I've attached sample of the script, chart and data.  Date range is october, 2017 - current date.  Notice there is no March 2018.  Can I make that empty year/month 0?  Thank you for ideas!

I'm unsure whether best to set the $description ahead of time with all 0's then replacing the value or whether I should track the month number as I go and add the 0 when current month doesn't match prior or whether there is a better technique to either approach.  I worry because I'll be crossing year boundaries so a simple + on month number will not work.

Inspections.fmp12

[jbrown]

Howdy. 

Your idea of setting each possible x-axis to 0 and then later updating it is a great idea. I took that and turned it into a JSON object where the key is "2017 Nov" and the value, to start is 0.

My version of the script uses your basic look. I updated each value from 0 to the correct summary value, using your great method of the fast summaries (which I have just recently learned).

I then had to create the $x and the $y by looping again through the possible dates and extracting the value from the key.

 

Take a look. It's the "GatherData" script.

Since charts need all possible axis points, I would usually recommend a summary table to run and add a record for 2018 Mar even if the amount is 0. But the JSON object is a good substitute, especially using the (amazing) fast summaries method.

Inspections_JSON.fmp12

Edited March 12, 2019 by jbrown
Finished a thought.

[LaRetta]

Thank you, Jeremy.  I have used the method of specifying empty values before, picked up from Michael Horak (Comment) a few years back but it was when setting a flat 12-month (predictable) period where I could also hard-code the x axis (monthNames) but since I wanted an unspecified number (and names) of x axis, I was of course stuck!

Just before you posted, I finished including an unspecified number of 0s but I disabled it instead deciding to include it in the loop itself (result is attached).  I look forward to studying your creation since I'm just getting my head deeply into JSON and the more methods I understand, the better.   😀

Note:  I decided to use true dates when incrementing through the months because then I wouldn't have to test whether I crossed a year boundary.  It seemed simpler for me.

 

InspectionsMOD2.fmp12

Edited March 13, 2019 by LaRetta
i replaced it with MOD2 since I realized it stopped the loop if no more records but we actually want it to generate the report to the end regardless.

[LaRetta]

BTW, I want to restrict the x axis if over a certain number since the date range is open-ended.  I realized I could use:

12 * ( Year ( EndDate ) - Year ( StartDate ) ) + Month ( EndDate ) - Month ( StartDate ) 

... a calculation provided by Comment awhile back instead of the twisted calc I started to use in generating the x axis zeros (in the disabled section of my script).

[LaRetta]

4 hours ago, jbrown said:

I took that and turned it into a JSON object where the key is "2017 Nov" and the value, to start is 0.

Actually, I don't see a value of using JSON here and it means looping twice.  I appreciate the attempt anyway!

[jbrown]

8 hours ago, LaRetta said:

Actually, I don't see a value of using JSON here and it means looping twice.  I appreciate the attempt anyway!

Yeah. I had to do this to get the dates in the right order. There's no guarantee the keys will be sorted how I want them to be. If I had done 2017 11 Nov it'd be okay, and the items would stay in the right order.

The other option is to do an array, since arrays can be any order. I was working on adjusting it.

That said, the re-loop add milliseconds, so I didn't see the actual harm in looping again.