topic Re: Problem with rolling in QlikView

Problem with rolling

Thu, 29 May 2014 06:54:53 GMT

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

jonathandienst — Thu, 29 May 2014 07:04:56 GMT

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

Thu, 29 May 2014 07:57:04 GMT

See atach, not give me the spected result..

can you do an example?

Re: Problem with rolling

richard_pearce6 — Thu, 29 May 2014 08:22:59 GMT

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

Thu, 29 May 2014 08:25:38 GMT

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

Thu, 29 May 2014 08:28:48 GMT

Hi,

Use this Exp in Pivot table,

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

Regards,

Prabhu

Re: Re: Problem with rolling

Thu, 29 May 2014 08:49:26 GMT

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

IT SUM ALL, NOT A SOLUTION

Re: Re: Problem with rolling

Thu, 29 May 2014 08:52:31 GMT

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

richard_pearce6 — Thu, 29 May 2014 09:24:17 GMT

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

Re: Problem with rolling

Thu, 29 May 2014 13:32:47 GMT

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