Jump to content
Sign in to follow this  
LaRetta

Filling in month gap

Recommended Posts

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

Share this post


Link to post
Share on other sites
Posted (edited)

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 by jbrown
Finished a thought.
  • Thanks 1

Share this post


Link to post
Share on other sites
Posted (edited)

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 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.

Share this post


Link to post
Share on other sites

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).

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.