Columns by Period

[#1]

Hi,

I try to create a table with weekly debt for last 13 weeks. For that I'm using a field called Period which contains Year + week= 201650, 201651, 201652, 201701, 201702, ...201709.

I need to get one table like below excel table, and for every weekly debt I'm using one counter of week that I put in the file which start en 1 when Period=201601, 52 for Period=201652, 53 for Period =201701 and go on. This counter is useful for obtain weekly debts, but I have troubles for labels = period when I passed from year 2017 to 2016...

Could you help me please?

Area	SubArea	Name cust.	201648	201649	201650	201651	201652	201701	201702	201703	201704	201705	201706	201707	201708
Area 1	Subarea 1	Cliente 1	-$ 1,399,145	$ 882,258	$ 876,337	$ 876,337	-$ 1,384,411	-$ 1,371,162	$ 876,337	$ 870,415	$ 858,573	$ 858,573	$ 852,652	$ 2,173,624	$ 2,173,624
Area 2	Subarea 2	Cliente 2	$ 1,045,914	$ 1,082,615	$ 1,075,349	$ 1,544,522	$ 1,548,910	$ 1,570,228	$ 1,580,909	$ 1,715,777	$ 1,839,722	$ 1,866,660	$ 1,853,787	$ 1,840,913	$ 1,840,913
Area 3	Subarea 3	Cliente 3	$ 73,279	$ 1,563,842	$ 1,553,347	$ 1,553,347	$ 57,713	$ 57,160	$ 1,524,572	$ 1,514,271	$ 1,493,669	$ 1,493,669	$ 1,483,367	$ 1,473,066	$ 1,473,066
Area 4	Subarea 4	Cliente 4	$ 28,456	$ 28,266	$ 28,076	$ 28,076	-$ 2,472,617	-$ 2,448,953	$ 28,076	$ 344,880	$ 340,187	$ 340,187	$ 337,841	$ 569,297	$ 1,350,589
Area 5	Subarea 5	Cliente 5	$ 0	$ 1,351,747	$ 1,342,675	$ 1,342,675	$ 0	$ 0	$ 1,342,675	$ 1,333,603	$ 1,315,458	$ 1,315,458	$ 1,306,386	$ 1,297,314	$ 1,297,314
Area 6	Subarea 6	Cliente 6	-$ 84,945	$ 1,157,539	$ 1,149,770	$ 1,217,440	-$ 17,806	-$ 17,636	$ 1,217,440	$ 1,209,214	$ 1,192,762	$ 1,192,762	$ 1,184,536	$ 1,176,310	$ 1,176,310
Area 7	Subarea 7	Cliente 7	$ 0	$ 0	$ 0	$ 0	$ 0	$ 0	$ 0	$ 0	$ 0	$ 0	$ 0	$ 793,506	$ 791,568
Area 8	Subarea 8	Cliente 8	-$ 8,189	$ 706,684	$ 701,941	$ 701,941	-$ 8,102	-$ 8,025	$ 701,941	$ 697,198	$ 688,365	$ 688,365	$ 683,618	$ 678,871	$ 678,871
Area 9	Subarea 9	Cliente 9	$ 4,275	$ 4,247	$ 4,218	$ 413,763	-$ 56,550	-$ 56,009	$ 490,491	$ 487,176	$ 480,548	$ 480,548	$ 477,234	$ 473,920	$ 473,920
Area 10	Subarea 10	Cliente 10	$ 153,921	$ 152,895	$ 151,869	$ 151,869	-$ 144,978	-$ 143,591	$ 259,399	$ 257,647	$ 320,082	$ 320,082	$ 317,875	$ 315,667	$ 320,551
Grand Total	Grand Total	Grand Total	$ 6,688,650	$ 14,989,263	$ 14,499,669	$ 15,366,656	$ 2,729,740	$ 2,835,885	$ 14,316,245	$ 14,865,393	$ 15,063,165	$ 14,793,273	$ 14,177,398	$ 16,329,964	$ 17,058,568

[MarcoWedel]

maybe you could use a pivot table instead?

[#3]

OK, I will try...

[#4]

But a pivot table doesn't solve my problem with label = period which contains Year + week= 201650, 201651, 201652, 201701, 201702, ...201709.

I have troubles with labels = period when I passed from year 2017 to 2016...

[#5]

But a pivot table doesn't solve my problem with label = period which contains Year + week= 201650, 201651, 201652, 201701, 201702, ...201709.

I have troubles with labels = period when I passed from year 2017 to 2016...

The problem is that instead of Period is 201650, 201651, 201652, 201701, 201702... I obtained: 201698, 201699, 201700, 201701, 201702... for weeks 50, 51 and 52 from 2016 and weeks 01 and 02 for 2017....

[Anil_Babu_Samineni]

May be create Year Week of dimension and use that Dim directly and transpose in Pivot Table it will reach your target

You want to break them as First week for each year may be help ful

Redefining the Week Numbers

[anagharao · Accepted solution]

Hi Nancy,

The expression you are currently using will not work as the week and year column cannot be treated as numbers.

I've added in a dimension for the Year+Week column, derived date using MakeWeekDate funtion and used that to subtract 4 weeks (1 month) using set analysis and variables. You can modify the same for 3 months which would be 12 weeks of data.

Please find the solution attached.