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?
Here's a calendar that will manage these types of calculations.
http://community.qlik.com/docs/DOC-6593
Regards
Richard
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
Hi
Can you share your qvw file?
Thanks
Sabal
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)
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
Hi
PFA QVW file created by using dummy data.
Try this and let me know.
Thanks
Sabal
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
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)
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
Hi Prem,
Did you get a chance to look into it? Can you please help me with these calculations?
Thanks