Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
sannidhikumar
Creator
Creator

how to get date format

Hi All ,

how to get date format .

and i am using this date(date#([Due Date],'DD-MMM-YYYY'),'MMM-YYYY') AS MonthYear1 not getting correct

output.

to Ex: Jan-2017

thanks ,

45 Replies
sannidhikumar
Creator
Creator
Author

kamal ,

i understand your point but i need output in this format by reducing year and month.

how can i achieve using that format.

Capture2.JPG

shanky1907
Creator II
Creator II

Use the below expression:

=UPPER(Date(Date#([Due Date],'YYYY-MM-DD'),'MMM-YY'))

And in the sort tab keep the Text format in Center for your Dimension

sannidhikumar
Creator
Creator
Author

shashank ,

if i am using upper it is showing in capital EX: JAN-17,FEB-17 i am looking for which is shown below pic.

thanks

shanky1907
Creator II
Creator II

You asked to see in the same format:

kamal_sanguri
Specialist
Specialist

Now I am really curious about it.. What I understood is the format of your source column which is [Due Date] is 'DD-MMM-YYYY' i.e 15-Jan-2017 like this and You want that to convert to a different format which is 'MMM-YY' (Jan-17)

or 'MMM-YYYY' (Jan-2017) or 'YYYY-MM-DD' (2017-01-15).


Use this function and set the format as per your need.


=Date(Date#([Due Date],'DD-MM-YYYY'),'YYYY-MM-DD')


Change the highlighted to 'MMM-YY'  if you want (Jan-17)

Change the highlighted to 'MMM-YYYY'  if you want (Jan-2017)

Change the highlighted to 'DD-MMM-YYYY'  if you want (15-Jan-2017)

Change the highlighted to 'DD-MM-YYYY'  if you want (15-01-2017)

Change the highlighted to 'DD-MM-YY'  if you want (15-01-17)

Change the highlighted to 'DD-MMM-YY'  if you want (15-Jan-17)

Change the highlighted to 'DD-MMMM-YY'  if you want (15-January-17)

Change the highlighted to 'DD-MMMM-YY'  if you want (15-January-17)

Change the highlighted to 'DD-MMMM-YY'  if you want (15-January-2017)

Change the highlighted to 'YYYY-MMM-DD'  if you want (2017-Jan-15)

.

.

.

Hope looking at the above examples, you would be able to change the format as per your needs yourself.

sannidhikumar
Creator
Creator
Author

hi shashank ,

after using this i am getting this

  UPPER(Date(Date#([Due Date],'YYYY-MM-DD'),'MMM-YY')) as MonthYear1

Capture.JPG

kamal_sanguri
Specialist
Specialist

IF the format of your source column [Due Date] is YYYY-MM-DD'


Use this function and set the format as per your need and change the highlight per your needs as mentioned in previous reply.


=Date(Date#([Due Date],'YYYY-MM-DD'),'YYYY-MM-DD')


shanky1907
Creator II
Creator II

Can you please post a sample qvw file. Just take the Due Date in it and reload and attach?

sannidhikumar
Creator
Creator
Author

QUALIFY *;

UNQUALIFY HIER_ID;

CustomerLedger_vw_QlikView:

LOAD ActualReserve,

     ActualReserve_USD,

     Bucket,

     BusinessUnit,

     BusinessUnitDescription,

     CO,

     Collector,

     CollectorName,

     CompanyCode,

     COMPANYID,

     ContractStatus,

     ContractStatusDesc,

     ContractType,

     ContractTypeDesc,

     CurrCode,

     CustNo,

     CustomerName,

     Diff,

     DocNo,

     DocPayItem,

     DocTy,

     Date(Date#([Due Date],'YYYY-MM-DD'),'MMM-YY') as MonthYear1,

     //Date(Date#([Due Date],'DD-MM-YYYY'),'MMM-YYYY')as MonthYear1,

     year([Due Date])*100+month([Due Date]) as periodno,

     //Date(Monthstart([Due Date]), 'MMM-YYYY') as MonthYear1,

     [Due Date] AS Due_Date,

  Month([Due Date]) & '-' & Year([Due Date]) AS MonthYear,

     FX_CONV_RT,

     GrossAmount,

     GrossAmount_USD,

     HIER_ID,

     InvoiceDate,

     InvoiceRef,

     InvoiceRefNo,

     LOB,

     LOBDescription,

     NationalAccount,

     NationalAccountDescription,

     NoOfInvoices,

     NoOfUnits,

     OpenAmount,

     OpenAmount_UC,

     OpenAmount_USD,

     OpenOverDue120,

     OpenOverDue180,

     OpenOverDue360,

     OpenOverDue60,

     OpenOverDue90,

     ParentCustomer_Number,

     ParentCustomerName,

     PaymentTerm,

     PaymentTermDesc,

     Pkey as PKEY,

     ProjectNo,

     SellRep,

     SellRepName,

     Tax,

     Tax_USD,

     TaxableOpenAmount,

     TaxableOpenAmount_USD,

     TotalNoOfInvoices,

     TotalNoOfUnits,

     TranAmt,

     TranAmt_USD,

     TranCurr,

     TranOverDue90,

     [WO G/L Date]

FROM

$(QVDPath)CustomerLedger_vw_QlikView.qvd

(qvd);

UNQUALIFY *;

this is the table i am using shashank.

sannidhikumar
Creator
Creator
Author

shashank ,

this is the fact table i am using to get the output.
so how i can achieve it .

thanks ,