Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Panv
Contributor II
Contributor II

Using greater than for date variable in set expression

Im trying to calculate the 12 week rolling average prior to the most current week

The most recent date is stored in variable $(vMaxCal) as an integer (currently it is 44365 for 6/18/2021)

To get the 12 week average I need to get date field (timeofarrival) to be (> vMaxCal - 12 weeks) essentially

how can I fix this set expression so that it works :
=sum({< [Publications.timeofarrival]={">date($(vMaxCal)-(7*12),'M/D/YYYY')"}, [_thisWeek]-={1}>} Publications.counter) /12

Btw, if i hard code the date in like this, it works:
=sum({< [Publications.timeofarrival]={">3/26/2021"}, [_thisWeek]-={1}>} Publications.counter) /12

also to add, the equation itself doesnt have any problem in terms of error messages, but the result in the visualization is a value of 0 instead of the actual average>

If i create a KPI with JUST date($(vMaxCal)-(7*12),'M/D/YYYY'), it works

1 Solution

Accepted Solutions
GaryGiles
Specialist
Specialist

You need to enclose the date calculation in a $-expansion, like this:

=sum({< [Publications.timeofarrival]={">$(=date($(vMaxCal)-(7*12),'M/D/YYYY'))"}, [_thisWeek]-={1}>} Publications.counter) /12

View solution in original post

2 Replies
GaryGiles
Specialist
Specialist

You need to enclose the date calculation in a $-expansion, like this:

=sum({< [Publications.timeofarrival]={">$(=date($(vMaxCal)-(7*12),'M/D/YYYY'))"}, [_thisWeek]-={1}>} Publications.counter) /12

View solution in original post

Panv
Contributor II
Contributor II
Author

Ahh perfect, thank you Gary!