Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help on Set Analysis

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

8 Replies
Not applicable
Author

Check date format on vEndDt and docDate so you don't compare apples with pears.

forte
Partner - Creator
Partner - Creator

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

Not applicable
Author

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

forte
Partner - Creator
Partner - Creator

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

sridhar240784
Creator III
Creator III

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

Not applicable
Author

hi, forte

thanks for quick reply, I am trying in script what you suggest

anil

Not applicable
Author

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

Sokkorn
Master
Master

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