use max date < today in set analysis

[curiousfellow]

I have a calendertable with startdates, enddates  and periodnames

I have sales with periodnames

Now I want to sum sales with set analysis where max(enddate) < today()

Because i did not succeed i first tried to find the max date with :

=max({<date(floor(MasterCalander.Enddate),'DD-MM-YYYY') = {'< $(date_today)'} >}MasterCalander.enddate)

date_today is a variable containing function today()

I don''t get any result. Searched this forum a lot, but nothing works.

What is wrong ??

[Oleg_Troyansky · Accepted solution]

Hi Johan,

there are a few problems here:

1. Any Set Analysis filter should look like this:  

FieldName = {values}

You can't formulate a Set Analysis filter starting with a function.

2. Depending on the format of the variable date_today (dual or numeric), you may need to use it differently in Set Analysis.

I'd suggest to use an Advanced Search condition (enclosed in double quotes and beginning with an equal sign), that helps with both issues. Something like this following should work:

SUM(

          {<MasterCalander.Enddate = {"=max(MasterCalander.Enddate) < date_today"} >}

          Sales)

In Advanced Search conditions, date fields can be freely compared to numeric fields and variables don't need to be enclosed in a $-sign expansion.

cheers,

Oleg Troyansky

Learn Set Analysis and many other advanced techniques in my book QlikView your Business.