Split yearly total into a monthly total?

NickHoff — Tue, 17 Dec 2013 13:46:02 GMT

I have a data source like the following:

Date	FY Total	# of Months	Monthly Total
7/1/2010	$2,220,984	12	$185,081.97
8/1/2010	$7,423,377	1	$7,423,377.38
9/1/2010	$1,034,324	2	$517,161.99
10/1/2010	$2,582,660	3	$860,886.67
11/1/2010	$630,291	4	$157,572.71
12/1/2010	$631,738	5	$126,347.53
1/1/2011	$1,481,003	6	$246,833.86
1/11/2011	$33,351	6	$5,558.58
2/1/2011	$811,158	7	$115,879.66
3/1/2011	$718,391	8	$89,798.88
3/31/2011	$4,828	8	$603.52
4/1/2011	$1,235,130	9	$137,236.65
5/1/2011	$298,936	10	$29,893.57
6/1/2011	$1,335,654	11	$121,423.06

I want to display it like this based on the number of months (divide the yearly total by number of months to get the monthly total and spread it across the months.

Date	Jul 2010	Aug 2010	Sep 2010	Oct 2010	Nov 2010	Dec 2010	Jan 2011	Feb 2011	Mar 2011	Apr 2011	May 2011	Jun 2011	FY Total	# of Months	Monthly Total
7/1/2010	$185,082	$185,082	$185,082	$185,082	$185,082	$185,082	$185,082	$185,082	$185,082	$185,082	$185,082	$185,082	$2,220,984	12	$185,082
8/1/2010	$7,423,377		-	-	-	-	-	-	-	-	-	-	$7,423,377	1	$7,423,377
9/1/2010	$517,162	$517,162	-	-	-	-	-	-	-	-	-	-	$1,034,324	2	$517,162
10/1/2010	$860,887	$860,887	$860,887	-	-	-	-	-	-	-	-	-	$2,582,660	3	$860,887
11/1/2010	$157,573	$157,573	$157,573	$157,573	-	-	-	-	-	-	-	-	$630,291	4	$157,573
12/1/2010	$126,348	$126,348	$126,348	$126,348	$126,348	-	-	-	-	-	-	-	$631,738	5	$126,348
1/1/2011	$246,834	$246,834	$246,834	$246,834	$246,834	$246,834	-	-	-	-	-	-	$1,481,003	6	$246,834
1/11/2011	$5,559	$5,559	$5,559	$5,559	$5,559	$5,559	-	-	-	-	-	-	$33,351	6	$5,559
2/1/2011	$115,880	$115,880	$115,880	$115,880	$115,880	$115,880	$115,880	-	-	-	-	-	$811,158	7	$115,880
3/1/2011	$89,799	$89,799	$89,799	$89,799	$89,799	$89,799	$89,799	$89,799	-	-	-	-	$718,391	8	$89,799
3/31/2011	$604	$604	$604	$604	$604	$604	$604	$604	-	-	-	-	$4,828	8	$604
4/1/2011	$137,237	$137,237	$137,237	$137,237	$137,237	$137,237	$137,237	$137,237	$137,237	-	-	-	$1,235,130	9	$137,237
5/1/2011	$29,894	$29,894	$29,894	$29,894	$29,894	$29,894	$29,894	$29,894	$29,894	$29,894	-	-	$298,936	10	$29,894
6/1/2011	$121,423	$121,423	$121,423	$121,423	$121,423	$121,423	$121,423

Re: Split yearly total into a monthly total?

jerem1234 — Tue, 17 Dec 2013 15:28:53 GMT

See if attached is what you are looking for. It was a bit tricky, but this is the formula I used for expression:

if(if(max(total <Date>aggr($(eMonthlySavings)/CalcMonth, Date)) <> 0,mod(num(month(CalendarMonthAndYear))+5, 12)+1) <= max(total <Date> aggr(CalcMonth, Date)), max(total <Date>aggr($(eMonthlySavings)/CalcMonth, Date)))

This might not be the most optimized formula, but it works.

Hope this helps!

topic Re: Split yearly total into a monthly total? in QlikView

Split yearly total into a monthly total?

Re: Split yearly total into a monthly total?