Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Where "Date" condition is not working

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();
3 Replies
MVP
MVP

Re: Where "Date" condition is not working

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

Not applicable

Re: Where "Date" condition is not working

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

Not applicable

Re: Where "Date" condition is not working

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

Community Browser