Date Equal to Weekend Date in Set Analysis

[#1]

I need to include the condition POS date equal to Friday in the following expression:

Avg(Aggr(Sum({$<CCYTD={1}>}OnHandDol),POSDate))

I have tried to get this to work a number of different ways including:

• using an if condition in the expression
• using multiple different syntax forms of POSDate = Weekend(POSDate)
• using an if condition before the expression

each of these returns either the wrong value or nothing at all. Any idea of how to get this expression to work correctly? Any help would be greatly appreciated.

Thanks,

Ben D.

[swuehl · Accepted solution]

You can use weekday(DATEFIELD) to get the weekday, Friday equals to 4.

So try a set expression like

Avg(Aggr(Sum({$<CCYTD={1}, POSDate = {"=weekday(POSDate)=4"}>} OnHandDol),POSDate))

[MayilVahanan]

Hi,

     Try like this,

     =WeekEnd(Today(),0,-2) gives friday day..

     Avg(Aggr(Sum({$<CCYTD={1},PosDate={$(WeekEnd(PosDate,0,-2))}>}OnHandDol),POSDate))

     or

     if(PosDate = WeekEnd(PosDate,0,-2),Avg(Aggr(Sum({$<CCYTD={1}>}OnHandDol),POSDate)))

     Hope it helps

[#4]

Thank you both for the help.

Swuehl--your expression is what I was looking for. I was previously close to that answer; however, I was using WeekEnd(POSDate)+5, which wasn't working.

Mayil--I tried both of your expressions. The first one (when looked at in a straight table) was returning all the days of the week (I'm not sure why). The second one returned nothing. Thank you for your help anyhow.