Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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.
maybe you could use a pivot table instead?
OK, I will try...
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...
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....
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
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.