Where "Date" condition is not working

[#1]

Hi Guys, does any body can tell me why this Where condition is not working?? What I need is to get the sales of this month not before this month, I tryed this but is not working, any ideas?? I attached the APP for a better understand.

Thank you Guys!!

ODBC CONNECT TO QV_DMT_TRANSMISIONES (XUserId is PIfLdKNKSSLGFKdIfKTaUPNKESZB, XPassword is YDEZYcVMUTMGTcUNObdGTEA);

SQL

SELECT

CEBE,

CIA,

"COD_CLIENTE",

COMMODITY,

COUNTRY,

"CTO_MAT",

"CTO_USD",

CUENTA,

"CURRENCY",

CUSTOMER,

"CUSTOMER_ORIG",

"CVE_CD",

"CVE_SECTOR",

DATE,

"DIST_CHANNEL",

"FACTURA_II",

"IMP_USD",

INFOTYPE,

INVOICE,

MARKET,

MATERIAL,

MODEL,

"NOM_CUENTA",

"NVO_PROD",

PLANT,

PRODUCT,

QTY,

"SALES_MO",

"SALES_MXN",

SECTOR,

SHIP,

TYPE,

UM

FROM

"DMT_TRANSMISIONES".dbo."FACT_QV_SALES"

WHERE DATE  > MonthStart(Today()) AND

DATE  <= Today();

[jonathandienst]

Hi

Your Where clause is part of a SQL SELECT statement. This statement is executed by the database server and not by the Qlikview script. Monthstart and Today are Qlikview operators that are not understood by the database server and so will not work here.

The normal solution is to put the dates into variables, and $ expand the variables to make a valid SQL Where clause.

Before the SQL Select, define the variables:

Let vToday = Date(Today(), 'YYYY-MM-DD');

Let vMonthStart = Date(MonthStart(Today(), 'YYYY-MM-DD');

... and in the Where clause:

Where DATE > '$(vMonthStart)' AND DATE <= '$(vToday)'

(Adjust the data format as required so that it matches the date formats for your database locale)

HTH

Jonathan

[#3]

Hi Guys thanks for your Replys I tryed both advices, but I can´t get the correct date, any Idea why is this wrong Date?? I attached the app reloaded.

Thank you Guys!!

[#4]

Your expressions appear to work correctly. Did you ever resolve the problem?