Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hi,
I am still waiting for reply on this. Can someone throw me any suggestions?
Thanks
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])
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
The following blog will be informative on this subject:
The Magic of Set Analysis – Point In Time Reporting | iQlik - Everything QlikView.
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
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
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
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
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