topic Re: Columns by Period in QlikView

Columns by Period

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Columns by Period

MarcoWedel — Tue, 28 Feb 2017 19:23:11 GMT

maybe you could use a pivot table instead?

Re: Columns by Period

Tue, 28 Feb 2017 19:25:34 GMT

OK, I will try...

Re: Columns by Period

Tue, 28 Feb 2017 19:34:44 GMT

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...

Re: Columns by Period

Tue, 28 Feb 2017 19:36:54 GMT

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....

Re: Columns by Period

Anil_Babu_Samineni — Wed, 01 Mar 2017 02:47:05 GMT

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

Re: Columns by Period

anagharao — Wed, 01 Mar 2017 08:49:07 GMT

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.