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?

[richard_pearce6]

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

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

Regards

Richard

[#3]

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

[#4]

Hi

Can you share your qvw file?

Thanks

Sabal

[preminqlik]

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)

[#6]

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

[#7]

Hi

PFA QVW file created by using dummy data.

Try this and let me know.

Thanks

Sabal

[#8]

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

[preminqlik]

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)

[#10]

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

[#11]

Hi Prem,

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

Thanks