topic Date Function to get start dates and Past 12 months in QlikView

Date Function to get start dates and Past 12 months

Tue, 18 Nov 2014 03:39:57 GMT

Hi Friends Stuck with Date Functions,I Have a Accounting Period Field with all Dates the Format is 0201406, 0201405, 2014050 2014060

Have written below script

Load

[@28:30] as [Prod],

[@31:40] as [Pol],

[@41:46] as [Cert],

[@47:56] as [Claim],

[@57:63] as [AccPrd],==>Date Formats differently need to get in common format of 'YYYYMM'

[@1:3]&[@57:63] as Key,

Date#(Left(Num([@57:63]),6),'YYYYMM') as [Accounting Period]==>YYYYMM'

My main problem is while writing Where [Accounting Period]]>=$(vPrevious) and [Accounting Period]]<=$(vCurrent) and NOT Exists(Key) ;

Error:Garbage after statement,can any one help on this.

How to get Accounting Period as DDMMYYYY with make date function so that i can get 12 months of Data from QVD.

As Accounting Period contains YYYYMM while writing Logic getting Error can any one help on this to write Make date Functions on Accounting Period

Re: Date Function to get start dates and Past 12 months

Tue, 18 Nov 2014 03:57:19 GMT

Accounting Period Has 'YYYYMM' Need to Get as 'DDYYYYMM' format where i can use Below condition

Getting Error can any one suggest on how to get 'DDYYYYMM'

My main problem is while writing Where [Accounting Period]]>=$(vPrevious) and [Accounting Period]]<=$(vCurrent) and NOT Exists(Key) ;

Variables Used:

vCurrent=MonthEnd(MONTHSTART(TODAY())-1)

vPrevious=AddMonths(MONTHSTART(Today()),-13)

Re: Date Function to get start dates and Past 12 months

SergeyMak — Tue, 18 Nov 2014 04:01:51 GMT

Hi,

You can use string concatenation like

'01' & [AccPrd] AS Date, if you need to load as first day of the month

Regards,

Sergey

Re: Date Function to get start dates and Past 12 months

Tue, 18 Nov 2014 04:10:24 GMT

Not Working for any calendar comparison of Between Statement

01201410(DDYYYYMM) to 20201211(DDYYYYMM)

But in My case I have only YYYYMM in Account Period How to make Account Period as DDYYYYMM format

Make Date (Account Period)

Can you help on this

Re: Date Function to get start dates and Past 12 months

SergeyMak — Tue, 18 Nov 2014 04:16:23 GMT

It would be faster if you post your app.

But probably you can do something like this:

Use Variables

vCurrent=Date(MonthEnd(MONTHSTART(TODAY())-1),'DDYYYYMM')

vPrevious=Date(AddMonths(MONTHSTART(Today()),-13),'DDYYYYMM')

And for AccPeriod:

Date(MakeDate(LEFT(AccPrd,4)),RIGHT(AccPrd, 2), 1,'DDYYYYMM')

Regards,

Sergey