topic Re: Date function not functioning properly in QlikView

Date function not functioning properly

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

when i am seeing my sold_date field in my dasboard its giving correct values of date till 2017 but not for 2018(attached image)

why? When I am selecting sold_date the format for 2018 date is changed and sold_year field which is derived from sold_date is not updated with date 2018 why?

this is a following script:-

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Initial Catalog=ETPEAS;Data Source=192.168.1.70;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=QLIKVIEW;Use Encryption for Data=False;Tag with column collation when possible=False](XPassword is XEDJTZFMLB);

Increment_Sales:
Concatenate(POS_Master_final)
Load
Warehouse as ETPCode,
InvoiceNumber as [inv no],
Date#(CreateDate, 'YYYYMMDD') as Sold_Date,
month(Date#(CreateDate, 'YYYYMMDD')) as Sold_Month,
year(Date#(CreateDate, 'YYYYMMDD')) as Sold_Year,
replace(ItemNumber,' ','') as itemnumber,
left(ItemNumber,3) as [Season Code],
InvoiceQuantity as quantity_sold,
SalesPrice as [sales price],
LocalAmount as [inv value],
if ( LocalAmount <500, '<500', if ( LocalAmount <800, '500-799',if ( LocalAmount <1200, '800-1199',if ( LocalAmount <1600,'1200-1599',if ( LocalAmount <2500,'1600-2499',if ( LocalAmount <3500,'2500-3499',if ( LocalAmount <5000,'3500-4999','>=5000'))))))) as [Inv val Bucket],
VatAmount as [tax amount];
SQL SELECT *
FROM ETPEAS.dbo.CashOrderTrn
WHERE InvoiceType = '31' and (CreateDate >= 20130101 and CreateDate <= '$(currentdate)') ;

Increment_Returns:
Concatenate(POS_Master_final)
LOAD
Warehouse as ETPCode,
SalesReturnNumber as [return no],
Date#(CreateDate, 'YYYYMMDD') as Sold_Date,
month(Date#(CreateDate, 'YYYYMMDD')) as Sold_Month,
year(Date#(CreateDate, 'YYYYMMDD')) as Sold_Year,
replace(ReturnItemNumber,' ','') as itemnumber,
left(ReturnItemNumber,3) as [Season Code],
ReturnQuantity as quantity_returns,
SalesPrice as [sales price],
-LocalAmount as [inv value],
if ( LocalAmount <500, '<500', if ( LocalAmount <800, '500-799',if ( LocalAmount <1200, '800-1199',if ( LocalAmount <1600,'1200-1599',if ( LocalAmount <2500,'1600-2499',if ( LocalAmount <3500,'2500-3499',if ( LocalAmount <5000,'3500-4999','>=5000'))))))) as [Inv val Bucket];
SQL SELECT *
FROM ETPEAS.dbo.SalesReturnTrn
WHERE InvoiceType = '31' and (CreateDate >= 20130101 and CreateDate <= '$(currentdate)') ;

master calender

for i=2013 to 2018

for j=1 to 12

step1:

LOAD

RecNo(),

MakeDate($(i),$(j),RecNo()) as Sold_Date

AutoGenerate(31);

NEXT j;

NEXT i;

step2:

LOAD Sold_Date,

year(Sold_Date) as year,

month(Sold_Date) as Month,

Date(monthstart(Sold_Date), 'MM-YYYY') as MonthYear,

day(Sold_Date) as Day,

QuarterName(Sold_Date) as Quater1,

WeekDay(Sold_Date) as weekday,

if(Ceil(Month(Sold_Date)/3)=1,'Q1',IF(Ceil(Month(Sold_Date)/3)=2,'Q2',IF(Ceil(Month(Sold_Date)/3)=3,'Q3',IF(Ceil(Month(Sold_Date)/3)=4,'Q4')))) as Quarter

date(Sold_Date) as OrderDate

Resident step1;

drop Table step1;

Re: Date function not functioning properly

Clever_Anjos — Fri, 12 Jan 2018 10:48:13 GMT

How do you calculate currentdate?

Re: Date function not functioning properly

Anil_Babu_Samineni — Fri, 12 Jan 2018 10:48:37 GMT

Perhaps this?

Alt(Date(Date#(CreateDate, 'YYYYMMDD')), Date(Num(CreateDate))) as Sold_Date,

Re: Date function not functioning properly

Anonymous — Mon, 15 Jan 2018 09:19:37 GMT

Letcurrentdate =DATE(Today(1)-1,'YYYYMMDD');

Re: Date function not functioning properly

kamalqlik — Mon, 15 Jan 2018 09:37:40 GMT

Hi Saksham,

Is your Sold_Date in Fact table giving the correct value.

Regards

Kamal

Re: Date function not functioning properly

Colin-Albert — Mon, 15 Jan 2018 15:57:48 GMT

Have a look at this post on dates. The Date Function

You may want to use Date(Date#(....)) to ensure your dates are processed consistently.

e.g. In Increment Returns

change the line Date#(CreateDate, 'YYYYMMDD') as Sold_Date,

to Date(Date#(CreateDate, 'YYYYMMDD')) as Sold_Date,

Re: Date function not functioning properly

Anonymous — Tue, 16 Jan 2018 07:40:55 GMT

Here currentdate is not working in script

Incremental_Non_Etp_Returns:

Concatenate(POS_Master_final)

Load

//ApplyMap('SAPCODE_ETPCodeMapping',NCode,'') as ETPCode,

NCode as SAPCode,

InvoiceNumber as [return no],

Date(SalesDate, 'YYYYMMDD') as Sold_Date,

month(Date#(SalesDate, 'YYYYMMDD')) as Sold_Month,

year(Date#(SalesDate, 'YYYYMMDD')) as Sold_Year,

replace(ItemNumber,' ','') as itemnumber,

left(ItemNumber,3) as [Season Code],

//'N_ETP SALE' as SaleType,

SaleQuantity as quantity_returns,

-NetValue as [inv value],

MRPValue as [sales price],

if ( NetValue <500, '<500', if ( NetValue <800, '500-799',if ( NetValue <1200, '800-1199',if ( NetValue <1600,'1200-1599',if ( NetValue <2500,'1600-2499',if ( NetValue <3500,'2500-3499',if ( NetValue <5000,'3500-4999','>=5000'))))))) as [Inv val Bucket],

-TaxAmount as [tax amount];

SQl Select WH.GoodsSender as NCode, SD.InvoiceNumber, SD.SalesDate,SD.ItemNumber,

SD.SaleQuantity, SD.NetValue,SD.MRPValue,SD.TaxAmount

FROM SAP_Link.dbo.Sales_DataNonETP_BIReports SD

INNER JOIN ETPEASV55.dbo.warehouse_sap_bireport WH

ON SD.CustomerId=WH.Warehouse

where SD.Doc_Type = 'ZIKR' and SD.SalesDate >= 20130101 and SD.SalesDate <= '$(currentdate)' ;

Re: Date function not functioning properly

Peter_Cammaert — Tue, 16 Jan 2018 08:57:53 GMT

That is because the $-sign substitution places the QlikView expression in a SQL SELECT statement instead of the calculated value. That won't work as most if not all RDBMS do not understand QlikView functions.

It seems that you stuffed the string "= Date(Today()-1, 'YYYYMMDD')" in variable currentdate, instead of a value like "20180114" (yesterdays date). Are you sure the answer you posted to Clevers question is correct? It doesn't look like it.

Check the current value of currentdate in Settings->Variable overview. What script statement did you really use to initialise that variable? Also check whether there aren't multiple statements that try to set currentdate.