topic Re: Rolling Date in App Development

Rolling Date

Anonymous — Wed, 22 Aug 2018 13:40:30 GMT

I am trying to create a measure that will use data only from last 30 days, but have not been able to get the correct syntax for my expression:

SUM(aggr(IF(floor([completed_date.autoCalendar.Date])>=num(today())-30 and floor([completed_date.autoCalendar.Date])<= num(today()),[completed_date.autoCalendar.Date],null()), SUM(aggr(sum({$<order_type={'sales'}>}distinct quantity), order_product_id))))

Any help to modify this expression? Thanks in advance.

Re: Rolling Date

ValeriyaBartosh — Wed, 22 Aug 2018 14:04:33 GMT

Hello, may be try to use set analysis https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/ChartFunctions/SetAnalysis/set-analysis-expressions.ht…

sum ( {<Date={">=$(=MonthStart(Today(2),0)) <=$(=MonthEnd(Today(2),0))"},Year =,Month=,Week=> } Something)
sum ( {<Date={">=$(=Date(Today(2)-30)) <=$(=Date(Today(2)-1))"},Year =,Month=,Week=> } Something)

Re: Rolling Date

BalaBhaskar_Qlik — Wed, 22 Aug 2018 15:00:31 GMT

Try this:

=SUM(Aggr(Sum(Distinct {$<Order_type={'sales'}, [completed_date.autoCalendar.Date] =

{">=$(=Date(max([completed_date.autoCalendar.Date])-30, 'DD/MM/YYYY'))"}>}Quantity), order_product_id))))

Re: Rolling Date

BalaBhaskar_Qlik — Wed, 22 Aug 2018 15:01:04 GMT

May be this:

=SUM(Aggr(Sum(Distinct {$<Order_type={'sales'}, [completed_date.autoCalendar.Date] = {">=$(=Date(max([completed_date.autoCalendar.Date])-30))"}>}Quantity), order_product_id))))

Re: Rolling Date

Anonymous — Wed, 22 Aug 2018 15:46:34 GMT

Thank you!! This worked in applying rolling date to measure.

Re: Rolling Date

BalaBhaskar_Qlik — Wed, 22 Aug 2018 15:53:47 GMT

Thank you.