Thanks Lei for the effort. I was able to count the number of times it is OUT OF STOCK. I see possibility in your approach if the counter can be reset to 1 after it notices a IN STOCK status ot after 14 days. Can you please share how did you come up with this count?
If you can make this partition, can you make a LOD calculation on it?
If you are taking daily measurements (not taking into account weekends and so on) :
Days Out Of Stock by Partition
If you're not taking into account weekends, etc, you should be able to subtract the MIN([Date]) from the MAX([Date])+1 to get days.
Then a simple calc can help you determine if there is an EVENT or not:
IF [Days Out Of Stock by Partition] >= 10 then "Event" else "Not critical" end
//Here 10 is equivalent to 10 business days, of 14 days. You can modify this to 14 if you're subtracting min/max dates.
Naturally, this means a single Event could be 14 days or 14 years-- it wouldn't ring the alarm for every day after the the 14.
Maybe this is the direction you were heading? --Michael
Hello Michael, The data I'm using MS Access, which did not support the Fixed function.
My apologies, I should have mentioned it in my question.
I am also looking at it like this:
If STOCK OUT =1 day then check next day,
if next day is stock out then count as only one day and keep doing this until 14 days.
Reset the counter after 14 days.
Something like this...any thoughts?
This is a little tricky without seeing the data (could you publish a .twbx?).
What about creating an index or rank by partition? You could count it and compare int([Index Count]/14) to [Index Count]/14. If they are equal, you'll know you've found the 14th occurrence per partition.
I'm probably over simplifying this since some calculations blow up when you try to aggregate them... but it might just work. Thoughts? --Michael
I see possibility in your approach if the counter can be reset to 1 after it notices a IN STOCK status ot after 14 days. Can you please share how did you come up with this count?
This is right the point where I am stuck at.
Please find the details in the attached workbook.
I have a question about logical.
The dates, at least in your sample data, are not continuous.
For example, there's not 8/13 and 8/14 (maybe because it's weekend?)
When counting the days of "OUT OF STOCK", should the missing dates be counted?
This is a matter of,
1) counting the number of "Y"s in the partition, or
2) finding the date difference from the first out of stock date in the partition.
Count event.twbx 19.5 KB
1 of 1 people found this helpful
This is great work, Lei Chen
It looks like my suggested method won't work, for reasons Bora Beran outlined in another post:
"LOD expressions are done in the database, table calculations are computed locally on your computer. That's the reason why you can't mix the two the way you tried. It would work the other way e.g. you could consume the output of LOD calcs inside a table calculation.
You could do all of this as table calculations though. You could use window_sum without a moving window at group level as well. If you set partition setting to Group in Edit Table Calculation > advanced... dialog. Your window becomes Group so you will get the sum for the group."
He might be a good resource, though.
Another thought: since this is in Access, are you able to add calculations that would count occurrences and flag these events? It's fairly straight forward in Excel. Here's wishing you good luck! --Michael