Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All,
Any one please help on set analysis, I can found that what I am doing wrong.
please help, attaching a sample here
thanks
anil
Check date format on vEndDt and docDate so you don't compare apples with pears.
Hi Anilsknp ,
As Thomas said, try cheking date format for docdate field on your load script ( just using date(docdate) as docdate each time yo load docdate on your script) and it will work correctly.
Hope it helps ,
Regards
thanks to both of you for reply,
but can u explain more what you are saying and can u correct me in my attached QV example what I am doing wrong.
Thanks & Regards
anil
Hi Anilsknp ,
I just copy your load script and just paint , in bold and underlined , what you need to change in order to solve your problem ...
Party:
LOAD iPartyId,
sPartyName,
SalesRep,
sPartyGroupName,
unit1,
unit2,
unit3,
sml,
prtZone;
SQL SELECT *
FROM ERPDATA.dbo.viewQVBasicSalesParty;
SML:
LOAD iPartyId,
SML;
SQL SELECT *
FROM ERPDATA.dbo.viewQVBasicSalesPartyTonsSML;
Machine:
LOAD macId,
sMachineNo,
sMacTypeName,
sPaperTypeName,
iTPD,
iTPM,
iTPY,
posId,
sMacPositionName,
sMacSubPositionName,
iOMacSpeedTo,
iInvLength,
iInvWidth;
SQL SELECT *
FROM ERPDATA.dbo.viewQVBasicSalesMachineDet;
ProductGroup:
LOAD iItemGroupId,
sItemGroupName,
grpName;
SQL SELECT *
FROM ERPDATA.dbo.viewQVBasicSalesProductGroups;
FinYear:
LOAD
Date(docDate) as docDate,
DayNo,
month(docDate) as month,
MonthName(docDate) as mntName,
mntNo,
Quarter,
curQuarter,
fyYear1,
fyYear2,
fyDate1,
fyDate2,
curFyDate1,
curFyDate2,
nxtFyDate1,
nxtFyDate2,
lst1Date,
lst2Date,
lst3Date,
lst4Date,
lst5Date,
lst1FyDate1,
lst1FyDate2,
lst2FyDate1,
lst2FyDate2,
lst3FyDate1,
lst3FyDate2,
lst4FyDate1,
lst4FyDate2,
lst5FyDate1,
lst5FyDate2,
lst5FinYear,
lst4FinYear,
lst3FinYear,
lst2FinYear,
lst1FinYear,
"Financial Year",
curFinYear,
nxtFinYear;
SQL SELECT *
FROM ERPDATA.dbo.viewQvFinanceCurNxtLstFinYear;
MaxSalDate:
LOAD "Financial Year",
fyMaxSalTrnDate;
SQL SELECT *
FROM ERPDATA.dbo.viewQvFinMaxSalTrnDate;
Finance:
LOAD trnType,
docId,
docNo,
Date(docDate) as docDate,
sourceNo,
sourceDate,
iSrNo,
iPartyId,
iPymtCommitDays,
iMaxPymtCommitDays,
iItemGroupId,
sItemName,
iWireNo,
posId,
iLength,
iWidth,
Sqmtr,
iAgrPrice,
iAgrRate,
iInvoiceAmountINR,
iExport,
sPymtModeName,
sDocMediaName,
invGrTotal,
collAmt,
crnAmt,
outStndAmt,
outStndAgeToDays,
recdOn,
recdInDays;
SQL SELECT *
FROM ERPDATA.dbo.viewQvFinanceZZZFinal;
InvDet:
LOAD sInvNo,
Date(docDate) as docDate,
invAmt,
dVchDate,
iAmount;
SQL SELECT *
FROM ERPDATA.dbo.viewQvFinanceInvDetail;
Regards
Your date formate in Docdate and Variable are in different formates.
Variable $(vEndDt) Returns - 4.8098505739755e-005
Docdate has MM/DD/YYYY hh:mm:ss TT formate.
if i properly understood your requirement, You need to create you Docdate with out timestampe and equate same with variable $(vEndDt)
Hope i`m clear.
-Sridhar
hi, forte
thanks for quick reply, I am trying in script what you suggest
anil
hi, Sri
Thanks for your reply
I am trying to get EndDate of every month and then I am trying to use it in Set analysis. to get the outstanding value for the each month.
anil
Hi Anil,
Two thing to do:
1. Change variable vEndDt = DATE(monthend(max(docDate)) ,'M/D/YYYY hh:mm:ss TT')
2. Change expression =max(vEndDt) to =vEndDt
See the sample attached file also.
Let me know if this one help you.
Regards,
Sokkorn Cheav