Rolling Month Count

[Saskia272]

Hi everyone, 

I have a question concerning a rolling month count.

The situation is: 

I have table with a field with different numbers of suppliers and this field ist connected to the same in another table which also contains supplier numbers and a date. So the reason for this connection is to get the date.

Now I want to show the number of suppliers over 12 Months. So for example For 2019 I need the sum of the distinct count of supplier numbers from Feb 2018 until Jan 2019. 

Has anyone an idea? 🙂 

I tried with the dimension year.month and distinct count of the vendor numbers but I only get the distinct value for the month not together within the sum of the last months. 

Thank you very much in advance! 

[rubenmarin]

Hi maybe using an aggr to distribute the value for each month and sum al values, like:

Sum(Aggr([your expression to count one month], YearMonthField))

Maybe you also need a set analysis to lmiit data for twelve month, like:

Sum({<DateField={">$(=Date(Addmonths(Max(DateField), -12)))<=$(=Max(Date))"}>} [Your expression also with set analysis] YearMonthField))

[Saskia272]

Thank you very much for your answer!

The second solution did not worked.. but with the frist one I got a soluten but its the same if I just do a simple Count distict on the vendornumbers and year.moth as demension it shows the number per month but not the sum of he numbers and it shows from the beginning of 2018 but I only need 2019. 

So for January 2019 I need all the numbers from Feburary 2018 including Jan 2019 as a Sum of the distict count of the vendor numbers.. 

[rubenmarin]

If you have a dimension with months you can set the accumulation in the measure, maybe you need an updated version of Sense)