"Order Date" not found while using where conditon..

[purna]

Hello qlikers,

Encountering the error that field "Order Date" is not found,but when commenting the where condition its running fine why?

Mapping_People:
Mapping LOAD
Person,
Region

FROM [lib://DataFiles/Superstore .xls]
(biff, embedded labels, table is People$);

Orders:
LOAD
"Row ID",
"Order ID",
"Order Date" ,
"Ship Date",
"Ship Mode",
"Customer ID",
"Customer Name",
Segment,
Country,
City,
State,
"Postal Code",
Region,
ApplyMap('Mapping_People',Region,Region) as region,
"Product ID",
Category,
"Sub-Category",
"Product Name",
Sales,
Quantity,
Discount,
Profit
FROM [lib://DataFiles/Superstore .xls]
(biff, embedded labels, table is Orders$)

where ["Order Date"] >= $(#vl.Startdate) and ["Order Date"] <= $(#vl.Enddate);

Hear i had used variables in other section to fetch the value for last 88 days.

set vl.Numberofdaystoload =88;

Let vl.Basedate = Max(Date);

Let vl.Startdate =AddMonths(MonthStart($(#vl.basedate)),$(#vl.Numberofdaystoload)*-88);
Let vl.Enddate=MonthEnd($(#vl.basedate));

Please solve the issue and help me in this..

Thanks in advance...

Mapping_People:
Mapping LOAD
Person,
Region

FROM [lib://DataFiles/Superstore .xls]
(biff, embedded labels, table is People$);

Orders:
LOAD
"Row ID",
"Order ID",
"Order Date" ,
"Ship Date",
"Ship Mode",
"Customer ID",
"Customer Name",
Segment,
Country,
City,
State,
"Postal Code",
Region,
ApplyMap('Mapping_People',Region,Region) as region,
"Product ID",
Category,
"Sub-Category",
"Product Name",
Sales,
Quantity,
Discount,
Profit
FROM [lib://DataFiles/Superstore .xls]
(biff, embedded labels, table is Orders$)

where ["Order Date"] >= $(#vl.Startdate) and ["Order Date"] <= $(#vl.Enddate)

[tresesco]

You have to remove either of square brackets or quotes to make this work but not both. Try like:

where  "Order Date">=...

[purna]

Thanks Tresrsco,

Its working but the value of sum(sales ) are get changing.

Where "Order Date" >= $(#vl.Startdate) and "Order Date" <= $(#vl.Enddate)
and "Order Date" <= $(#vl.Basedate);

and also my max date 23/10/2017 but in frontend showing 11/30/2021.

My Query:

I want last 88days sales value using where clause dynamically.

i tried like this as below;

set vl.Numberofdaystoload =88;

Let vl.Basedate = Today();//Max(Date("Order Date"));
Let vl.Startdate = AddMonths(MonthStart($(#vl.Basedate)),$(#vl.Numberofdaystoload)*-1);
Let vl.Enddate = MonthEnd($(#vl.Basedate));

And call those variables in to the frontend and showing dates as shown in screenshot.

please let me know any changes in this.

[tresesco]

Hi @purna ,

So it's a different issue. You have to know what and why of this. If you are using today() for max, you can't get  2017 date. Also for startdate, you are using addmonths() to subtract days which is wrong. You can subtract a days by just subtracting directly like:

vRange= $(vMax)- $(NoOfDays) 

Try to debug the code in the script to understand why this output is so.

[purna]

Thankyou Tresesco,

Can i get a script for this scenario please.

As i am learner and new to the qlik can't  able to find the errors in the script.

Please post the complete script in backend,so that it would be helpful to others also.

Regards....

[tresesco]

How do you get your max date? Does it come from a field?

[purna]

Date(Max(date))