Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
How do you calculate currentdate?
Perhaps this?
Alt(Date(Date#(CreateDate, 'YYYYMMDD')), Date(Num(CreateDate))) as Sold_Date,
Letcurrentdate =DATE(Today(1)-1,'YYYYMMDD');
Hi Saksham,
Is your Sold_Date in Fact table giving the correct value.
Regards
Kamal
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,
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)' ;
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.