Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi I have this following expression below to calculate the # of orders that check in on the date itself
count(DISTINCT if(
aggr
( (Order_Date= Check_In_Date,Order_No),Order_No))
I need to include a condition like the statement below where it look @ rolling 7 days
Count(DISTINCT{<Order_Date= {'>=$(=date(max(all Order_Date)-7))'}>} (Order_No))
How can I use Max Date rolling 7 days on if?
Remember that I can only do on expression, not on dimension and script.
Thanks
Try like this
if(Order_Date= Check_In_Date,Count(DISTINCT{<Order_Date= {'>=$(=date(max(all Order_Date)-7))'}>} (Order_No)))
mine comes with aggr
count(DISTINCT if(
aggr
(
(Check_In_Date= Order_Date)
,Order_No
)
,Count(DISTINCT{<Order_Date= {'>=$(=date(max(Order_Date)-7))'}>} Order_No))))
not working for this code
hmm try like this
count(
DISTINCT {<Order_Date= {'>=$(=date(max(Order_Date)-7))'}>}
aggr( if(Order_Date= Check_In_Date ,Order_No),Order_No))
Why don't you add a flag to your table to avoid complex expressions where Order_Date= Check_In_Date?
if(Order_Date= Check_In_Date,1,0) as #SameDay
Expression
SUM( {< Order_Date = {'>=$(=date(max(Order_Date)-7))'} >} #SameDay)
or
Count( {< Order_Date = {'>=$(=date(max(Order_Date)-7))'} ,#SameDay ={1} >} DISTINCT Order_No )
Try this
count(
DISTINCT
aggr( if(Order_Date > date(Max(TOTAL Order_Date)-7) AND Order_Date= Check_In_Date ,Order_No),Order_No))