Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi I am trying to build a new field in my sales table called 'LTM Sales Value'. In this field I want to sum up the aggregate of the previous 12 months of sales £. My 'Reporting Period' field contains the date ie 31/07/13 and my 'Sales Value' field contains the sales for that period i.e £1000. I have copied a formula which someone else published and tried to edit it for my own need but it isn't correct. Can anyone please help?
sum({< Date={'>=$(=AddMonths(`REPORTING PERIOD`,-12)'}>} 'SALES VALUE') AS 'LTM SALES VALUE' ,
thanks,
Janet
try this
sum({< Date={'>=$(=AddMonths(`REPORTING PERIOD`,-12))'}>} 'SALES VALUE') AS 'LTM SALES VALUE'
Hi Liron, no that didn't work either. I get a script failed message when I reload. Here is my whole script with your insertion:
LOAD
PERIOD,
`CALENDER YEAR`,
`CALANDER MONTH`,
`CALENDER PERIOD`,
`CALENDER QUARTER`,
`FISCAL YEAR`,
`FISCAL QUARTER`,
`REPORTING PERIOD`,
INYEARTODATE(`REPORTING PERIOD`,$(VMAXDATE),0,4)*-1 AS CYYTDFLAG,
INYEARTODATE(`REPORTING PERIOD`,$(VMAXDATE),-1,4)*-1 AS PYYTDFLAG,
sum({< Date={'>=$(=AddMonths(`REPORTING PERIOD`,-12))'}>} 'SALES VALUE') AS 'LTM SALES VALUE';
SQL SELECT *
FROM MASTERCALENDAR;
Hi Janet,
Your script not works because of these 2 reasons .
1.Aggregation functions like Sum, Count.. cann't be used without group by clause.
2. SetAnalysis cannot be used in Script. Only can be used in chart expressions.
Celambarasan
Hi,
You can not use the set analysis in script load.
First of all why you want the aggregation to happen at script level, same thing you can handle on front end using the charts.
Have a look at the attached application for reference.
EDIT:- I am trying to upload the application but some problem is der, but for reference you can have a look at the link below.
The Magic of Set Analysis – Point In Time Reporting | iQlik - Everything QlikView.
Regards,
Kaushik Solanki
Thanks Guys, I was hoping to let my end users build thier own graphs without them having to learn about set analysis. Would have been ideal as a field. Never mind. Thanks for the help
Try this
sum({< Date={'>=$(=AddMonths(`'REPORTING PERIOD'`,-12))'}>} 'SALES VALUE') in the frond end
Thanks Jeroan but that doesn't work either it just keeps returning the value of the month's sales rather than the aggregate of the last 12 months
Have you already tried with the rangesum function?