5 Replies Latest reply: Aug 22, 2018 11:53 AM by Bala Bhaskar

Rolling Date

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

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

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

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

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