6 Replies Latest reply: Dec 21, 2016 7:15 AM by Vineeth Pujari

# Max Date field  - 7 on if statement?

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

• ###### Re: Max Date field  - 7 on if statement?

Try like this

if(Order_Date= Check_In_Date,Count(DISTINCT{<Order_Date= {'>=\$(=date(max(all Order_Date)-7))'}>} (Order_No)))

• ###### Re: Max Date field  - 7 on if statement?

mine comes with aggr

• ###### Re: Max Date field  - 7 on if statement?

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

• ###### Re: Max Date field  - 7 on if statement?

hmm try like this

count(

DISTINCT {<Order_Date= {'>=\$(=date(max(Order_Date)-7))'}>}

aggr( if(Order_Date= Check_In_Date ,Order_No),Order_No))

• ###### Re: Max Date field  - 7 on if statement?

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 )

• ###### Re: Max Date field  - 7 on if statement?

Try this

count(

DISTINCT

aggr( if(Order_Date > date(Max(TOTAL Order_Date)-7)  AND Order_Date= Check_In_Date ,Order_No),Order_No))