Max Date field - 7 on if statement?

[n1ef5ng1]

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

[avinashelite]

Try like this

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

[n1ef5ng1]

mine comes with aggr

[n1ef5ng1]

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

[avinashelite]

hmm try like this

count(

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

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

[vinieme12]

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 )

[vinieme12]

Try this

count(

DISTINCT

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