Count Consecutive Times a Calc is above 0

Hi all,

I have a report that displays how many times each of our stores has been in the Bottom 25% in a R12 period. I'm doing that using the Rank function, and then counting if it's in the bottom 25%, and that works fine.

Now they would like to see the number of times the store was consecutively in the bottom 25%. So, for instance, if the Store is in the bottom in May, was it in April? Was it in March? And then I'd count those times and display it.

I'm trying to find a way to count that in TARGIT, but not having much luck. Does anyone have an input?

Thanks,

Danilo

0

Comments

6 comments
  • Hi Danilo

     

    You don't write how the data is presented, so assumed it was with month on the X axis, and shops on Y axis. Otherwise if you data is different, and this doesn't work, or you can't make it work just say the word :) Also in my example I count months < 4000 turnover, and then if months are consecutive. 

    Furthermore my date criteria is the last 13 months, and then I hide the first month. This is so I can see if the first month of the last 12 months, was also consecutive. In my case I get 5 consecutive months.

    I made a new measure that have a value = 1 if last month was less than 4000 and the next month is as well. 

    if allcount(d1:0, 0, m1) = 1 then 0 else if PreviousMonth:(sum(-1, 0, m1)) 
    < 4000 and CurrentMonth:(sum(0, 0, m1)) < 4000 then 1 else 0

     

    The 

    if allcount(d1:0, 0, m1) = 1 then 0

    is just to make sure that the hidden month doesn't count as consecutive, don't think it will but I then I am sure :)  

    And then you just sum that measure in a new calculated column. 

    sum(all, 0, m2)

     

    0
  • Hi Kristopher, thanks!

    This solution almost works. The only thing is that I'd like to stop counting when they stop being consecutive.

    For instance, in my example below, I'd like to count only the months since May-2021 that were consecutively > 40mil, but it counts the ones past that as well.

    That's being my struggle with it. I know there must be a way! haha

    Thanks again,

    Danilo

    0
  • Hi Danilo, 

     

    So your result in your example should be 3 instead of 4, because you don't want to count the last available month, is that correct?

    0
  • Hi Kristoffer,

    In my example I'd count May-2021, Apr-2021 and Mar-2021. The past months would be ignored because Feb-2021 was <40mil.

    0
  • Hi Danilo,

    Look at this example:

    x: if sum(0, 0, m1) < 25 then 1 else 0
    y: if sum(0, 0, m2) = 1 then sum(-1, 0, m3, 0) + 1 else 0
    Consecutive last months less than 25: sum(d-1, 0, m3)
    0
  • Hi Ole, I will test this out and let you know, but it seems it will work!

    Thanks!

    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post