Help with aggreaged 12 months sales please?

[#1]

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

[lironbaram]

try this

sum({< Date={'>=$(=AddMonths(`REPORTING PERIOD`,-12))'}>} 'SALES VALUE') AS 'LTM SALES VALUE'

[#3]

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;

[CELAMBARASAN]

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

[kaushiknsolanki]

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

[#6]

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

[jjordaan]

Try this

sum({< Date={'>=$(=AddMonths(`'REPORTING PERIOD'`,-12))'}>} 'SALES VALUE') in the frond end

[#8]

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

[jjordaan]

Have you already tried with the rangesum function?