rolling chart with two date fields

[Gestion-PSD]

Hi Community!

We're trying to make a rolling chart for elements with ONDATE and OFFDATE dates (each date in one field). We need an 'Active Elements by month' rolling chart.

An element is active on a specific date if ONDATE is smaller than that date and OFFDATE is greater. Talking about months, an element is active in a specified month if ONDATE is smaller than Month Start, and OFFDATE is greater than Month End.

Searching in the community, we'd found a expression for an 'Active Elements this month' KPI that works fine :

Count({$<ONDATE={">=$(=Date(MonthStart(Today())))"},OFFDATE={">=$(=Date(Today()))"} >}ELEMENT)

But, obviously, it doesn't work on a rolling chart.

What is the rigth expression for that?

Thanks,

Lujan Torres.

[lironbaram · Accepted solution]

hi 

let us assume your chart dimension is a field call yearMnoth

than you can try something like 

count(if(yearMonth<=ONDATE and yearMonth<=OFFDATE,ELEMENT))

[Gestion-PSD]

Hi Liron,

That isn't exactly the expression we need, but definitely it put us on the rigth way.

We were looking for a complex expression and the answer was as simply as that.

Thanks!