topic Re: Date Equal to Weekend Date in Set Analysis in QlikView

Date Equal to Weekend Date in Set Analysis

Thu, 19 Jul 2012 16:45:53 GMT

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.

Re: Date Equal to Weekend Date in Set Analysis

swuehl — Thu, 19 Jul 2012 16:57:53 GMT

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))

Re: Date Equal to Weekend Date in Set Analysis

MayilVahanan — Thu, 19 Jul 2012 17:03:21 GMT

Hi,

Try like this,

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

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

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

Hope it helps

Re: Date Equal to Weekend Date in Set Analysis

Thu, 19 Jul 2012 18:25:39 GMT

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.