YTD Fiscal Year Calculations

[#1]

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?

[#2]

Hi,

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

Thanks

[Anonymous]

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])

[#4]

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

[nagaiank]

The following blog will be informative on this subject:

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

[#6]

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

[#7]

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

[#8]

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

[#9]

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

[#10]

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