Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Columns by Period

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?

   

AreaSubAreaName cust.201648201649201650201651201652201701201702201703201704201705201706201707201708
Area 1Subarea 1Cliente 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 2Subarea 2Cliente 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 3Subarea 3Cliente 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 4Subarea 4Cliente 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 5Subarea 5Cliente 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 6Subarea 6Cliente 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 7Subarea 7Cliente 7$ 0$ 0$ 0$ 0$ 0$ 0$ 0$ 0$ 0$ 0$ 0$ 793,506$ 791,568
Area 8Subarea 8Cliente 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 9Subarea 9Cliente 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 10Subarea 10Cliente 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 TotalGrand TotalGrand 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
1 Solution

Accepted Solutions
Highlighted
Creator II
Creator II

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.

View solution in original post

6 Replies
Highlighted

maybe you could use a pivot table instead?

Highlighted
Not applicable

OK, I will try...

Highlighted
Not applicable

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

Highlighted
Not applicable

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

Highlighted

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator II
Creator II

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.

View solution in original post