topic Re: YTD Fiscal Year Calculations in QlikView

YTD Fiscal Year Calculations

Tue, 22 Jul 2014 14:54:47 GMT

Hi,

I am trying to do the set analysis calculations for MTD,YTD for this year and last year but i am unable to get the desired values.

I have a fiscal date and i want to have the set analysis defined for sales such that if i pick a date it must give me the MTD sales until that date and same with YTD and also for last year.

Can someone please help me with any suggestions?

Re: YTD Fiscal Year Calculations

Wed, 23 Jul 2014 11:37:38 GMT

Hi,

I am still waiting for reply on this. Can someone throw me any suggestions?

Thanks

Re: YTD Fiscal Year Calculations

Anonymous — Wed, 23 Jul 2014 11:45:55 GMT

Hi Try this

YTD:

Sum({$<PeriodID = {"<=$(=Max(PeriodID))"},

Year = {$(=Max(Year))},

Quarter = ,

Month = ,

Period = >} [# Departures Performed])

MTD:

Sum({$<PeriodID = {$(=Max(PeriodID))},

Year = ,

Quarter = ,

Month = ,

Period = >} [# Departures Performed])

Re: YTD Fiscal Year Calculations

Wed, 23 Jul 2014 12:13:09 GMT

Hi,

From your Fiscal date create Calendar with Financial_Quarter,Financial_year...

For MTD try like this with your Date Field :

sum({<CalYear={$(=max(CalYear))},CalMonth={$(=month(addmonths((max(CalDate)),-1)))}>}Sales)

For YTD:

=(sum( {<CalDate = {">=$(= Date (min( {<CalMonth=, CalYear=, CalQuarter=>} QuaterStartDate )))<=$(vMaxDate))" }, CalYear=, CalMonth=, CalQuarter=>} Sales))

For YTD (Year to Year):

sum({<CalDate = {">=$(=Date(Yearstart(max(CalDate))))<=$(=max(CalDate))"},CalMonth=,CalQuarter=>}Sales)

Hope it will help you to proceed.

Regards,

Harshal

Re: YTD Fiscal Year Calculations

nagaiank — Wed, 23 Jul 2014 12:17:45 GMT

The following blog will be informative on this subject:

The Magic of Set Analysis – Point In Time Reporting | iQlik - Everything QlikView.

Re: YTD Fiscal Year Calculations

Wed, 23 Jul 2014 12:45:17 GMT

Hi,

I tried it this way and it didnt work for me,

=Sum({$<[Date Id] = {"<=$(=Max([Date Id]))"},

[Fiscal Year Id] = {$(=Max(Year))},

[Fiscal Quarter] = ,

[Fiscal Month] = ,

[Date Id] = >} [Traffic])

actually i also posted one more post with my sample QVW and the validation file. Can you please look at that one too may be that should give you a better idea?

How To Calculate Selected Date, Last year date, MTD,YTD

Thanks

Re: YTD Fiscal Year Calculations

Wed, 23 Jul 2014 12:45:48 GMT

Hi,

I tried it this way and it didnt work for me,

=sum({<[Fiscal Year Id]={$(=max(Year))},[Fiscal Month]={$(=month(addmonths((max([Date Id])),-1)))}>}Traffic)

actually i also posted one more post with my sample QVW and the validation file. Can you please look at that one too may be that should give you a better idea?

How To Calculate Selected Date, Last year date, MTD,YTD

Thanks

Re: YTD Fiscal Year Calculations

Wed, 23 Jul 2014 12:55:38 GMT

Hi,

You can create 5 variables:

vStartMTD =MonthStart([Fiscal date])

vStartYTD =YearStart([Fiscal date])

vStartPrevYTD =addyears(YearStart([Fiscal date]),-1)

vEndPrevTYD =addyears(YearEnd([Fiscal date]),-1)

vEndDate =[Fiscal date]

And for calculating MTD, YTD and Previous YTD, you the below set analysis:

MTD =sum({<[Fiscal date] = {">=$(vStartMTD)<=$(vEndDate)"}>}Sales)

YTD =sum({<[Fiscal date] = {">=$(vStartYTD)<=$(vEndDate)"}>}Sales)

Prev YTD =sum({<[Fiscal date] = {">=$(vStartPrevYTD)<=$(vEndPrevYTD)"}>}Sales)

Let me know if this works.

Thanks

Sabal

Re: YTD Fiscal Year Calculations

Wed, 23 Jul 2014 13:11:40 GMT

Hi Sabal,

Can you please check the syntax for this one?

=sum({<[Date Id] = {">=$(vStartMTD)<=$(vEndDate)"}>}Traffic)

Because those 2 doesnt show up as variables they will be shown in color red and the value is displayed as 0. I think the syntax might be wrong.Can you please help me with this.

I didnt try remaining calculations as this is the first one i was working on this.

Thanks

Re: YTD Fiscal Year Calculations

Wed, 23 Jul 2014 13:35:44 GMT

Hi,

It is giving you an error because your [Date Id] field is not in the date format.

This is the reason why the ">=" and "<=" operators will not work.

Please try to change the field [Date Id] as follows in the script while loading:

date(date#([Date Id],'YYYYMMDD')) as XYZ // XYZ can be any field name

Let me know if you need more details.

Thanks

Sabal

Re: YTD Fiscal Year Calculations

richard_pearce6 — Wed, 23 Jul 2014 13:38:47 GMT

Here's a calendar that will manage these types of calculations.

http://community.qlik.com/docs/DOC-6593

Regards

Richard

Re: YTD Fiscal Year Calculations

Wed, 23 Jul 2014 13:48:30 GMT

Hi Sabal,

In my load script, i added this to the date

date(date#([Date Id],'YYYYMMDD')) AS [Fiscal Date]

and this gives me the date in date format and now i changed my set analysis to be something like this

=sum({< [Fiscal Date] = {">=$(vStartMTD)<=$(vEndDate)"}>}Traffic)

but still i get the value as 0 only, Any suggestions please?

Thanks

Re: YTD Fiscal Year Calculations

Wed, 23 Jul 2014 14:04:03 GMT

Can you share your qvw file?

Thanks

Sabal

Re: YTD Fiscal Year Calculations

preminqlik — Wed, 23 Jul 2014 14:08:06 GMT

hi try this

i assumed that fiscal year starts from april.

YTD:

sum({<[Fiscal Date] ={">=$(=makedate($(=Year(max([Fiscal Date] )),4))<=$(=max([Fiscal Date] ))"},YEAR=,MONTH=,DAY=>}Traffic)

MTD:

sum({<[Fiscal Date] ={">=$(=makedate($(=Year(max([Fiscal Date] )),$(=num(Month(max([Fiscal Date])))))<=$(=max([Fiscal Date] ))"},YEAR=,MONTH=,DAY=>}Traffic)

Re: YTD Fiscal Year Calculations

Wed, 23 Jul 2014 14:15:20 GMT

Hi,

My fiscal year starts in february.So, i tried these 2

=sum({<[Fiscal Date] ={">=$(=makedate($(=Year(max([Fiscal Date] )),2))<=$(=max([Fiscal Date] ))"},[Fiscal Year Id]=,[Fiscal Month]=,[Date Id]=>}Traffic)

=sum({<[Date Id] ={">=$(=makedate($(=Year(max([Date Id] )),2))<=$(=max([Date Id] ))"},[Fiscal Year Id]=,[Fiscal Month]=,[Date Id]=>}Traffic)

Fiscal date gives me the date in 1/1/2010 format where as date id gives me the date in 20100101 format.

But in both of these cases, i received the following error

Error: Error in set modifier ad hoc element list: '' or ')' expected

Re: Re: YTD Fiscal Year Calculations

Wed, 23 Jul 2014 14:25:15 GMT

PFA QVW file created by using dummy data.

Try this and let me know.

Thanks

Sabal

Re: YTD Fiscal Year Calculations

Wed, 23 Jul 2014 14:37:39 GMT

Hi Sabal,

Thanks for your help but the values seem wrong, one thing firstly is that the data in here for each day the traffic is shown for the whole week which means its aggregated up to the week level and the first day of the week shows the traffic for whole week, not sure if thats causing the difference or what not but the values seem off.If you look at my other post in which i sent you the QVW's i have attached the sample excel file with QVW.So, can you please try looking at it and see if that helps?

Thanks

Re: YTD Fiscal Year Calculations

preminqlik — Wed, 23 Jul 2014 15:02:17 GMT

oh shit here in make date, at year one ) is missing

i corrected it..use below expressions now below now

YTD:

sum({<[Fiscal Date] ={">=$(=makedate($(=Year(max([Fiscal Date] ))),4))<=$(=max([Fiscal Date] ))"},YEAR=,MONTH=,DAY=>}Traffic)

MTD:

sum({<[Fiscal Date] ={">=$(=makedate($(=Year(max([Fiscal Date]))),$(=num(Month(max([Fiscal Date])))))<=$(=max([Fiscal Date]))"},YEAR=,MONTH=,DAY=>}Traffic)

Re: YTD Fiscal Year Calculations

Wed, 23 Jul 2014 15:11:24 GMT

HiPrem,

I have tried the below calculation

=sum({<[Fiscal Date] ={">=$(=makedate($(=Year(max([Fiscal Date]))),$(=num(Month(max([Fiscal Date])))))<=$(=max([Fiscal Date]))"},[Fiscal Year Id]=,[Fiscal Month]=,[Date Id]=>}Traffic)

But still i receive the same error

Error: Error in set modifier ad hoc element list: '' or ')' expected

Re: YTD Fiscal Year Calculations

Wed, 23 Jul 2014 17:04:54 GMT

Hi Prem,

Did you get a chance to look into it? Can you please help me with these calculations?

Thanks