9 Replies Latest reply: May 29, 2014 9:32 AM by Amir Vastani

# Problem with rolling

Hi all,

I have this situation:

SEE(QVW).

I want to create a chart, pivot table( product, year month ---- like dimensions and sum(Sales) expresison and the rollongo of sum(Sales)...

I do: sum({\$<     DATE={"\$( ='>='&(date(monthend(max(DATE),-12)))&'<='&MONTHEND(MAX(DATE)) "} >} SALES)

Someone any idea?

The expected result is in yellow in the excel file.

• ###### Re: Problem with rolling

Hi

This is the correct syntax for your expression:

Sum({<DATE={">=\$(=MonthStart(Max(DATE),-12)) <=\$(=MonthEnd(Max(DATE)))"}>} SALES)

Edit: assumes that DATE is a date field formatted in the default date format for your system.

HTH

Jonathan

• ###### Re: Re: Problem with rolling

See atach, not give me the spected result..

can you do an example?

• ###### Re: Re: Problem with rolling

THE PROBLEM IS THAT NOT ACCUMULATE, ONLY SUM THAT COLUMN..

i NEED TO DO AGGR??? BY MONTH AND YEAR??? OR PUT IN SET ANALYSIS, YEAR=,MONTH=

• ###### Re: Problem with rolling

Hi Federico,

This post shows how to do simple point in time calculations using a Calendar table.

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

Regards

Richard

QlikCentral.Com

• ###### Re: Re: Problem with rolling

AND WITH THIS HOW DO YOU SOLVE MI PROBLEM,

i CREATED A CHART,

DIMENSION. COUNTRY YEAR AND MONTH

EXPRESION

1)SUM (SALES)

2) SUM({<Date={'\$(=max(Date))'},%Flag_PreviousMonth_M12={1} >} Sales)

Not work.

• ###### Re: Re: Re: Problem with rolling

Ah, there's a bug I didn't notice from the revised version I recently updated. I will fix.

For now if you goto the CLEAN UP tab and change the code:

Calendar:
Resident Calendar_Previous_Periods
where RangeMax(\$(v_RangeMaxString)) = 1
and %KeyDate >= Possible_KeyDate
;

Change the less than to a greater than as shown above and re-run.

That formula will give you %KeyDate 1-May-13 to 31-May-13 if you're \$(=max(Date)) = 29-May-14

• ###### Re: Problem with rolling

Hi,

Use this Exp in Pivot table,

RangeSum(Above(TOTAL sum(SALES),0,RowNo(TOTAL)))

Regards,

Prabhu

• ###### Re: Re: Problem with rolling

IT WILL BE A SOLUTION, BUT WATH ABOUT, IF YOU HAVE MORE THAN ONE YEAR??

IT SUM ALL, NOT A SOLUTION

• ###### Re: Problem with rolling

You need to create it in Load Script. Rolling Sum and Rolling Averages are best done in the Load Script from my adventures.

Take metric and create a copy of it, as MetricRolling and then move the Date field by 1 month or 1 week depending on the granularity needed by the metric for the number of rolling weeks/months etc. and then SUM() Group by this new MetricRolling field to condense it down with only needed dimensions for support

Finally, if you need to use the field it is a simple SUM(MetricRolling) and you are set