Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Gestion-PSD
Creator II
Creator II

rolling chart with two date fields

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.

1 Solution

Accepted Solutions
lironbaram
Partner - Master III
Partner - Master III

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))

View solution in original post

2 Replies
lironbaram
Partner - Master III
Partner - Master III

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
Creator II
Creator II
Author

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!