Sum from.. to.. in accordance with the data from another field

[stantrolav]

Hi there.

The task is to make countig for Artificially created year by selected month.

When I select 2012, April system must to calculate sum of value from 2011 May to 2012 April.

If I select 2011 and 2012, April system must to calculate sum of value

from 2011 May to 2012 April in first bar named 2012

and

from 2010 May to 2011 April in second bar named 2011

In attachment you'll find data with tables. MAT means Mathematical Artificial Time. MATStart is column with date 11 years before. I gues that in expression i need to use something like this

=sum( {$<Date= {">=$(MATStartDate)<=$(Date)"}>} PUB_EU)

But it dont work like i need.

MAT_from_to.qvw

[stantrolav]

Comment For Legendary gwassenaar

I saw you wonderfull description of how to make this in Calculating rolling n-period totals, averages or other aggregations

But it's not so helpfull for me. I think it's too difficult for understanding and using in my variant.

[stantrolav]

Could anyone help to solve it?

27 views from this discussion and 51 views from attachment makes me think that this nut is really difficult for this community. Who could prove professional skills?

[tresesco]

Take your calculation logic to script. May be like attached sample?

Important script portion:

RangeSum(

     Sales,peek('Sales'),Peek('Sales',-2),Peek('Sales',-3),

     Peek('Sales',-4),Peek('Sales',-5),Peek('Sales',-6),Peek('Sales',-7),

     Peek('Sales',-8),Peek('Sales',-9),Peek('Sales',-10),Peek('Sales',-11)

     ) as RollingSum

[stantrolav]

How could I use RangeSum() function in script for loading sum of Sales for 12 months, including selected?

[tresesco]

My attachment would give you the answer. In case you can't access the attachment, here is the script sample:

Sample_Int:
LOAD * INLINE [
    Month, Sales
    Jan-09, 1
    Feb-09, 2
    Mar-09, 3
    Apr-09, 4
    May-09, 5
    Jun-09, 6
    Jul-09, 7
    Aug-09, 8
    Sep-09, 9
    Oct-09, 10
    Nov-09, 11
    Dec-09, 12
    Jan-10, 13
    Feb-10, 14
    Mar-10, 15
    Apr-10, 16
    May-10, 17
    Jun-10, 18
    Jul-10, 19
    Aug-10, 20
    Sep-10, 21
    Oct-10, 22
    Nov-10, 23
    Dec-10, 24
    Jan-11, 25
    Feb-11, 26
    Mar-11, 27
    Apr-11, 28
    May-11, 29
    Jun-11, 30
    Jul-11, 31
    Aug-11, 32
    Sep-11, 33
    Oct-11, 34
    Nov-11, 35
    Dec-11, 36
];

Sample:
   Load
       Monthname(Date(Date#(Month,'MMM-YY'),'DD-MM-YYYY')) as YearMonth,
       Sales,
       RangeSum(
     Sales,peek('Sales'),Peek('Sales',-2),Peek('Sales',-3),
     Peek('Sales',-4),Peek('Sales',-5),Peek('Sales',-6),Peek('Sales',-7),
     Peek('Sales',-8),Peek('Sales',-9),Peek('Sales',-10),Peek('Sales',-11)
     ) as RollingSum
       
Resident Sample_Int;  // You might have to use ORDER BY

Drop Table Sample_Int;

Hope this helps.

[stantrolav · Accepted solution]

I found answer for my task.

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch

The solution is in this IntervalMatch() function.

IntervalMatch:

IntervalMatch (Date)

Load distinct FromDate, ToDate resident Intervals;

Thanks to Henric Cronström for this good post.