topic Re: YTD Creation in QlikView

YTD Creation

techvarun — Mon, 31 Jul 2017 07:43:50 GMT

Hi Qlixperts,

My Financial year is between Oct- Sep,

I need to store all the months in between the YTD Start and Current month-1 in a variable to use the same in a set analysis.

For Eg: YTDStart : OCT

Current Month : July

I want to store all the Months between Oct to June (CurrentMonth-1) in a variable like this.

vYTD:'Oct','Nov','Dec','Jan','Feb','Mar','Apr','May','Jun'

Your help is much appreciated

Re: YTD Creation

techvarun — Fri, 01 Sep 2017 07:16:09 GMT

Any ideas?

Re: YTD Creation

Anil_Babu_Samineni — Fri, 01 Sep 2017 07:21:02 GMT

Steps followed

1) I think you need to set up Fiscal Year for Oct - Sep

2) The variable should be this?

Sum({<Month = {">=$(=Min(Month)) <= $(=Max(Month)-1)"}>}Sales)

Or use NumberField for them and replace Month to NumberField

Re: YTD Creation

bharani8 — Fri, 01 Sep 2017 07:28:48 GMT

Few months back.. i have created a Qtr wise in the script itself.. May be this logic will be helpful in urs..

My year is like 201601.. 201602

left(mnthclosetime,4)+ if(right(mnthclosetime,2)>=11,1,0)&'-'& 'Q' & ceil(match(Month(makedate(left(mnthclosetime,4),right(mnthclosetime,2),'01')),'Nov','Dec','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct')/3) as Year_Subs_Quarter,

Re: YTD Creation

effinty2112 — Fri, 01 Sep 2017 07:45:08 GMT

Hi Varun,

Try this expression to give the Fin Year:

if (Ceil(Month(Date) / 3)<4,Year(Date)-1 & '/' & Year(Date), Year(Date) & '/' & Text(Year(Date)+1) )

For Date = 01/09/2017 the expression returns 2016/2017.

Regards

Andrew

PS This is probably simpler:

if (Month(Date) <10,Year(Date)-1 & '/' & Year(Date), Year(Date) & '/' & Text(Year(Date)+1) )

Re: YTD Creation

tomasz_tru — Sat, 16 Sep 2017 13:16:02 GMT

Is it possible to use flag on the data model?

Tomasz