use max date < today in set analysis

curiousfellow — Thu, 03 Nov 2016 15:02:41 GMT

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

Re: use max date < today in set analysis

Oleg_Troyansky — Thu, 03 Nov 2016 15:11:25 GMT

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.

topic Re: use max date < today in set analysis in QlikView

use max date < today in set analysis

Re: use max date < today in set analysis