topic Get total value from sum between two dates in QlikView

Get total value from sum between two dates

Thu, 04 Jul 2013 20:40:49 GMT

Good day everyone,

have been searching for this in many old discussions, but well here is my problem.

I am making a chart where in the rows are the values of some products selected. and each column has a date like this:

01/2013 - 02/2013 - 03/2013

value - value - value

This values are the total of the month and the prevous eleven ones.

Like this:

01/2013: 01/2013+12/2012+11/2012+10/2012+09/2012+08/2012+07/2012+06/2012+05/2012+04/2012+03/2012+02/2012

02/2013: 02/2013+01/2013+12/2012+11/2012+10/2012+09/2012+08/2012+07/2012+06/2012+05/2012+04/2012+03/2012

03/2013: 03/2013+02/2013+01/2013+12/2012+11/2012+10/2012+09/2012+08/2012+07/2012+06/2012+05/2012+04/2012

Here is an expression i was making by all the discussions i read.

= sum({<Date={">=$(max(prev_Date))<=$(max(Date))"}> } Value)

where

Date: field of the selected date or last date of the value in the certain case.

prev_Date: field of the date 11 month earlier

value: the value wanted from each date sum up

Date: 03/2013

prev_Date: 04/2012

Please help.

Re: Get total value from sum between two dates

Thu, 04 Jul 2013 21:40:06 GMT

use accumulate 12 step back:

eventdate	value	Sum (value)
01/01/2011	1147.88	1147.88
01/02/2011	47.29	1195.16
01/03/2011	70.18	1265.34
01/04/2011	10.53	1275.88
01/05/2011	4.50	1280.38
01/06/2011	180.94	1461.31
01/08/2011	177.89	1639.20
01/09/2011	50.63	1689.83
01/10/2011	50.54	1740.37
01/11/2011	49.61	1789.98
01/12/2011	73.72	1863.70
01/01/2012	50.33	1914.03
01/02/2012	54.10	820.25
01/03/2012	12.51	785.47
01/04/2012	4.74	720.03
01/05/2012	69.16	778.67
01/06/2012	1.58	775.75
01/07/2012	6.12	600.93
01/08/2012	122.84	545.88
01/09/2012	25.93	521.19
01/10/2012	150.41	621.05
01/11/2012	6.91	578.35
01/12/2012	18.17	522.80
01/01/2013	18.77	491.25
01/02/2013	4.68	441.83
01/03/2013	10.62	439.94
01/04/2013	4.58	439.78
01/05/2013	10.93	381.55
01/06/2013	2.45	382.41
01/07/2013	7.84	384.13
01/08/2013	5.65	266.94
01/09/2013	30.22	271.22
01/10/2013	12.76	133.58
01/11/2013	58.73	185.40
01/12/2013	6.54	173.77
01/01/2014	17.95	172.95
01/02/2014	39.29	207.55
01/03/2014	9.44	206.37
01/04/2014	7.10	208.90
01/05/2014	8.52	206.48
01/06/2014	8.67	212.70
01/07/2014	2.35	207.22
01/08/2014	3.69	205.26
01/09/2014	12.29	187.33
01/10/2014	14.75	189.32
01/11/2014	4.63	135.22

Re: Get total value from sum between two dates

Gysbert_Wassenaar — Fri, 05 Jul 2013 13:24:07 GMT

If you want to use the months as a dimension you can't use set analysis expressions because the sets are calculated per chart and not per dimension value. You can try using an AsOf table. See this document: Calculating rolling n-period totals, averages or other aggregations. It looks like you want a rolling 12-month total. The AsOfTable Examples document has an example of that.