Set analysis and operator and variable and function

[#1]

Hello,

I have been trying every combination I can think of without being able to solve my problem so I'm hoping you guys can help me.

It seems everytime I use set analysis and use set modifiers that use fields that combine functions, operators and variables, especially when dates are involved,there is a problem.

At the moment I have a chart that has an expression for sales.

I want to add an additional expression that has sales within the last 30 days.

This is the syntax for the expression so far.

sum( {$ <DATE = {">=$(=today()-30)"} > } SALES)

DATE contains dates formatted as YYYY-MM-DD

The result is 0 at the moment.

If I change the expression to:

sum( {$ <DATE = {">=2011-11-01"} > } SALES)

Then it works but I can not have a fixed date. It has to be 30 days from today.

I have also tried to create a variable that holds the value for today()-30 but this gives the date in number format and I haven't been able to change it into YYYY-MM-DD.

I suspect the problem is that Set analysis requires date comparisons to use the same format.

Is there anybody out there that can give me examples of the correct syntax to use when different variants are used.

E.g.

Function = <Modifier = {"=today()-1"} >

Operator = <Modifier = {">1"} >

Variable = <Modifier = {$(vInvoiceday-1)} >

Function combined with operator ?

Operator and variable?

Function and variable?

Function, variable and operator?

When reading up on this forum it is clear that the syntax for this function is a bit more complicated that other functions so it would also be very useful to get an explanation as to when one should use '', "", () (please add all known variants if I have missed any)

br

Martin

[Miguel_Angel_Baeyens · Accepted solution]

Hi Martin,

If your date does not have the document date format (see the script editor, the variables at the top of the main tab), then you need to transform either in the script (preferably) or in the expression

Sum({< DATE = {">=$(=Date(Today() - 30, 'YYYY-MM-DD'))"} > } SALES)

The function will return the same format your DATE values have.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

EDIT: Apart for the documentation and the hundreds of good posts in the QlikCommunity, take a look at this document in regards to syntax and functionality.