Numbers of Months (elapsed time) since a month when consecutive zeros are started


Hi Anaplaners,


I would like to figure out numbers of months elapsed since consecutive zeros have started. (please see the attached).

For instance, in the attached file, as sales of product BBB is zero since July 2019(instead of April 2019), I would like to get "4"(months) given current period is Oct 2019. 

Thanks in advance.




Best Answers

  • DavidSmith
    Answer ✓

    @VIGNESH.M @gregoryTan 

    There is, as ever, a much simpler approach

    1. Set up a time lookup module that holds the value of the current period.  Format the line item as time period - month2019-03-11_11-13-22.png


    2. Create three additional line items in your data module.  We will be utilising a little known technique that allows us to "reset" a cumulate count based on a boolean parameter2019-03-11_11-15-37.png

    Zero Count = IF Data > 0 THEN 0 ELSE 1

    Reset? = Zero Count = 0

    Cumulate Zero Count = CUMULATE(Zero Count, Reset?)

    3. Then you can link this to the summary module using the line item from 12019-03-11_11-14-35.png

    This allows for data within the run of zeros2019-03-11_11-33-45.png


    As a general note, remember that all calculations that only refer to time should be calculated in a time settings module rather than in multi-dimensional modules.






    I hope this hepls


  • gregoryTan
    Answer ✓

    Thanks Vegnesh for your response.

    What you have proposed is perfectly working. But another provided simpler solution, which I have to accept as a solution for this problem.

    Thanks again for your advise.


  • Hi Greg,


    Please follow the below steps,


    #1. Create a line item (Zero Month), set format as month and set Summary as last non blankPhoto1.png.jpg 

    Write the below logic in Zero month Line itemPhoto1.png.jpg


    #2, I have created another module to count the zero month, 



    There are there line item in the module, dimensions are Lists A and Line item

    Zero Month = Zero Count.Zero Month[SELECT: TIME.All Periods]


    Count = YEAR(Current Month) * 12 + MONTH(Current Month) - (YEAR(Zero Month) * 12 + MONTH(Zero Month))



  • HI @gregoryTan ,


    I have the similar idea as @DavidSmith explained. This will reduce one more line item from the above solution. Please follow the below steps to achieve this.


    Step 1: Create a Boolean Flag in your module and make it as Sales <> 0 (Best way to use not equal to to capture the negative values as well).

    Step 2 : Create another line item called "Cumulate" and put the formula CUMULATE(1,Boolean Flag)Numbers of Months (elapsed time) since a month when consecutive zeros are started.PNG

    Step 3: In your reporting module, Create 2 line items. One it to get the current period and another one is to get the count. Please find the below screen shot for formula refernce.

    Numbers of Months (elapsed time) since a month when consecutive zeros are started1.PNG

    @DavidSmith In this way I think we can optimise the model :).






  • @kavinkumar 

    I always try and split the line items out initially to exaplain the logic, but yes, there is scope for optimisation (Summary options cn be turned off, for example) and I agree with steps 1 and 2

    However, in step 3, you should not include the current period line item.  This is inefficient and over calculating,  duplicating the value across the products lists and is not needed.  Use the time lookup module as I outlined



  • @DavidSmith  I agree 🙂  it is always best practise to use time lookup in seperate module, for the time being I used it in this module.