precalculate mat in script

[#1]

Hi everyone,

i hope you can help me. I have to precalculate the moving annual total (mat) over the last 12 months in the script. The best i show you some sample data and the result which i want to reach.

source table:

Bill_ID	Date	Amount
1	02.05.2010	40
2	23.10.2011	76
3	19.08.2009	93
4	28.02.2010	82
5	10.03.2011	34
6	09.11.2009	65
7	17.04.2010	112
8	20.07.2010	28
9	21.06.2009	103
10	28.09.2011	39

target table:

Bill_ID	Date	Amount	MAT
1	02.05.2010	40	-
2	23.10.2011	76	-
3	19.08.2009	93	-
4	28.02.2010	82	-
5	10.03.2011	34	-
6	09.11.2009	65	-
7	17.04.2010	112	-
8	20.07.2010	28	-
9	21.06.2009	103	-
10	28.09.2011	39	-
-	21.06.2009	-	0
-	22.06.2009	-	0
-	...	-	...
-	01.07.2009.	-	103
-	02.07.2009	-	103
-	...	-	...
-	01.09.2009	-	196 (= 103 + 93)
-	...	-	...
-	01.08.2010	-	420 (= 93 + 65 + 82 + 112 + 40 + 28)
-	...	-	...
-	23.10.2011	-	149 (= 76 + 39 + 34)

So, e.g. when i want to calculate the mat for 1st august 2010, it should be sum all bills within the august 2009 and july 2010. Also, each cumulative value in a month should be the same as you see in july 2009.

I'm thinking and rethinking about this and how to solve it in qlikview but didnt come to any solution.

Can you provide any help?

Thanks in advance

[jagan]

Hi,

Please check the attachment for solution.

Regards,

Jagan.

[#3]

Hi,

thanks a lot for your solution. Now, I know what RangeSum does.

Still one question: assumed, i have 10000 bills within a period of 10 years. If i would precalculate the mat for the given last 12 months and save this for every date within the period, i would have about 3600 records. In the chart, I would only use the mat to visual the growth. Is it better to do this or to apply the rangesum function, from performance view?

Regards,

Philipp

[jagan]

Hi,

Applying the Expression would be better, because you need to use groupby to precalculate.  So, you may not be view accurately by dimensions.  If you do not need by dimensions then you can precalculate the values it is more better than calculating using RangeSum. 

I think for 10000 records, it is not a matter.

Regards,

Jagan.

[#5]

Hi,

i'm noticing when i select an unique month no mat will be calculated. Thats what you propably mean. But my aim is to visual the mat whether an unique month is selected or not. Then i had to calculate the mat in the script as you said.

But thats exactly what gives me headache. How do i do that?

Regards,

Philipp