Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Date Equal to Weekend Date in Set Analysis

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: Date Equal to Weekend Date in Set Analysis

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

3 Replies
MVP
MVP

Re: Date Equal to Weekend Date in Set Analysis

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

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

Not applicable

Re: Date Equal to Weekend Date in Set Analysis

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.

Community Browser