Hi James, a couple things first:
- Did you extract your data source before saving the packaged workbook? If not, please do that, package, and then upload the new version.
- You probably don't want 1 as the argument for PREVIOUS_VALUE(), because that's actually the number that PREVIOUS_VALUE will return if it can't find that calculation's value in the previous cell.
I have re-uploaded the document.
We could use a different default value for PREVIOUS_VALUE(), but in effect the query should work, as we had it working before with a slightly different data source.
It is interesting that it only fails when the _LastWeek part is added (I guess it is related to the fact that _LastWeek has mostly null values for anything other than the last 9 days from today.
Hi James, you can get rid of that weird issue by changing ATTR([Reported Timestamp]) to MIN([Reported Timestamp]). For some reason, using ATTR() there causes the fill-in for missing values to behave differently. However, this still causes a break in the LastWeek line for the weekend. Working on that part now.
Okay, I finally got it. It's a bit more complicated than I wish it could be, but basically, I had to use a running sum to determine when the prior week starts. I couldn't get it to work otherwise, but I'm not exactly sure why.
RUNNING_SUM(IIF(DATEDIFF('day', MIN([Reported Timestamp]), TODAY()) < 9, 1, 0))
IIF([LastWeek Filter] > 1, [Spot], NULL)
If this has been helpful, please do mark it as the correct answer so it can help others!
MMM_9_3.twbx 64.4 KB
Thanks David, that works perfectly.
I'm still confused why the other method does not work properly, if it is either a bug or a intended functionality, it seems to suggest the order of the calculation is not kept when both were fields were being charted.
Very impressed and thankful at how fast you spotted it!
Hi James, you're very welcome, and I'm glad I was able to solve the issue!
I'm also confused about why the other method doesn't work. It has something to do with the missing values filler, but I still don't know why sidestepping that issue with lookups didn't work. Oh, well!