Set Analysis

NickHoff — Fri, 13 Dec 2013 16:12:14 GMT

I've been beating my head against this for the past week and I'm getting closer to my solution. I am trying to find out a monthly total and spread it across that many months based on:

Implementation Date - Which has an alliance to my master calendar date

$(eTotalSavings) - Which is SUM(FY2000+FY2001+...FY2020)

CalcMonth - Which is PICK(MATCH(CalendarMonthName, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'), 6, 7, 8, 9, 10, 11, 12, 1, 2, 3, 4, 5) as CalcMonth

MonthNameandYear - Which is MonthName(TempDate) as CalendarMonthAndYear,

I created a variable for monthly total which is $(eTotalSavings/CalcMonth)

Now I have my monthly total in a pivot table table, which displays the following, but I want the monthly savings to spread across Implementation Date Start month + CalcMonth. So Jul should spread across 12 months while August 1 month and September 2 months ect how can this be accomplished with Set Analysis?

Date	CalendarMonthAndYear	Jul 2010	Aug 2010	Sep 2010	Oct 2010	Nov 2010	Dec 2010	Jan 2011	Feb 2011	Mar 2011	Apr 2011	May 2011	Jun 2011
7/1/2010		2220983.61	-	-	-	-	-	-	-	-	-	-	-
8/1/2010		-	7423377.38	-	-	-	-	-	-	-	-	-	-
9/1/2010		-	-	1034323.97	-	-	-	-	-	-	-	-	-
10/1/2010		-	-	-	2582659.997	-	-	-	-	-	-	-	-
11/1/2010		-	-	-	-	630290.82	-	-	-	-	-	-	-
12/1/2010		-	-	-	-	-	631737.664	-	-	-	-	-	-
1/1/2011		-	-	-	-	-	-	1481003.16	-	-	-	-	-
1/11/2011		-	-	-	-	-	-	33351.47333	-	-	-	-	-
2/1/2011		-	-	-	-	-	-	-	811157.6257	-	-	-	-
3/1/2011		-	-	-	-	-	-	-	-	718391.0425	-	-	-
3/31/2011		-	-	-	-	-	-	-	-	4828.12125	-	-	-
4/1/2011		-	-	-	-	-	-	-	-	-	1235129.877	-	-
5/1/2011		-	-	-	-	-	-	-	-	-	-	298935.66	-
6/1/2011		-	-	-	-	-	-	-	-	-	-	-	1335653.623

Re: Set Analysis

rachael_selman — Fri, 13 Dec 2013 16:16:30 GMT

Is it possible to post a sample application?

topic Set Analysis in QlikView

Set Analysis

Re: Set Analysis