Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
sakshamkaul
Contributor II

Date function not functioning properly

Hi

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;


7 Replies
Employee
Employee

Re: Date function not functioning properly

How do you calculate currentdate?

Re: Date function not functioning properly

Perhaps this?

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sakshamkaul
Contributor II

Re: Date function not functioning properly

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

Partner
Partner

Re: Date function not functioning properly

Hi Saksham,

Is your Sold_Date in Fact table giving the correct value.

Regards

Kamal

Re: Date function not functioning properly

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,

sakshamkaul
Contributor II

Re: Date function not functioning properly

Here currentdate is not working in script

painterrrrrooorrrr.png


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

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.