how to get date format

[sannidhikumar]

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 ,

[sannidhikumar]

kamal ,

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

how can i achieve using that format.

[shanky1907]

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]

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]

You asked to see in the same format:

[kamal_sanguri]

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]

hi shashank ,

after using this i am getting this

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

[kamal_sanguri]

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]

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

[sannidhikumar]

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]

shashank ,

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

thanks ,