Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
smcmilla
New Contributor II

How do you calculate the sum of sales for rolling 12 months and rolling months 13-24

Hi, Can someone help me with this syntax? Or suggest a better formula?

I need a rolling 12 months' sum of sales. This is my formula using today's date 11/14/2016 - 11/14/2017.

sum{<Date={"$(='>=' & date(today()-365) )"}>} Sales)


I also need the sum of sales for rolling months 13-24. This is my formula for 11/14/2015 - 11/14/2016

sum{<Date={"$(='>=' & date(today()-730) )"}>} Sales) - (sum{<Date={"$(='>=' & date(today()-365) )"}>} Sales)


Nothing seems to be working. I need it to work off today's date.

Any help is greatly appreciated.

5 Replies
atkinsow
Valued Contributor II

Re: How do you calculate the sum of sales for rolling 12 months and rolling months 13-24

Try

sum{<Date={'>=$(=date(today()-365))'}>} Sales)

smcmilla
New Contributor II

Re: How do you calculate the sum of sales for rolling 12 months and rolling months 13-24

Hi Wallo,

That did not return any results.

atkinsow
Valued Contributor II

Re: How do you calculate the sum of sales for rolling 12 months and rolling months 13-24

do you have some sample data?

Re: How do you calculate the sum of sales for rolling 12 months and rolling months 13-24

May be this

Last 12 Months

Sum({<Date={"$(='>=' & Date(AddYears(Today(), -1), 'MM/DD/YYYY') & '<=' & Date(Today(), 'MM/DD/YYYY'))"}>} Sales)

Months 13-24

Sum({<Date={"$(='>=' & Date(AddYears(Today(), -2), 'MM/DD/YYYY') & '<=' & Date(AddMonths(Today(), -1), 'MM/DD/YYYY'))"}>} Sales)

smcmilla
New Contributor II

Re: How do you calculate the sum of sales for rolling 12 months and rolling months 13-24

Hi everyone,

I wanted to share what I ended up using.

I created a variable called IsLast365Days in the calendar load. Then used it in my object.

Here is the code.

If (PeriodDate >= AddMonths(Today(),-12) and PeriodDate < Today(),1) as IsLast365Days,

Sum({<IsLast365Days = {1}, Year= , YearMonth = , Month = >}Sales)   this will give you the rolling 12 months of sales.

Community Browser