Problem with rolling

[#1]

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.

[jonathandienst]

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

[#3]

See atach, not give me the spected result..

can you do an example?

[richard_pearce6]

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

[#5]

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=

[#6]

Hi,

Use this Exp in Pivot table,

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

Regards,

Prabhu

[#7]

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

IT SUM ALL, NOT A SOLUTION

[#8]

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.

[richard_pearce6]

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:
NoConcatenate Load *
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

[#10]

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