Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
kevincagle
Contributor III
Contributor III

change over time

Hello,

My apologies if this is slightly confusing. I was trying to work through the problem as I typed this up.

I have 2 spreadsheets, each has a tables that has 20,000+ rows. One has 10 columns, the other 6.

The first is outlined in the sample provided here:Question: Use QlikView to compare year over year on line chart

SiteYearPersonnelCityStateMonth
Rack 120105St LouisMOJan
Rack 120106St LouisMOFeb
Rack 120101St LouisMOMar
Rack 120118St LouisMOJan
Rack 120119St LouisMOFeb
Rack 120116St LouisMOMar
Rack 22010

4

St LouisMOJan
Rack 220107St LouisMOFeb
Rack 2201013St LouisMOMar
Rack 2201114St LouisMOJan
Rack 220119St LouisMOFeb
Rack 2201117St LouisMOMar

There are 2 task I need to achieve.

1.) I need to get the values in the Personnel column for various Racks, located in multiple Cities/States to show total personnel and changes month to month, year over year on a chart. The problem I am having is the current way I am trying to do this seems to be multiplying identical values in Personnel by the total number of the identical entry, instead of factoring the change over time.

6 appears twice in Personnel, once in 2010, and 2011.

6*2=12.

With many City/State entries, it could mean 6 appears 50 to 60 times.

I have the following expressions:

sum({<Year = {'2010'}>}Personnel)

sum({<Year = {'2011'}>}Personnel)

The dimensions I am using : Month over Headcount

I cant seem to figure out what I am doing wrong but I know its something simple I am forgetting. The first thing that I see is SUM. I know that expression needs to be further broken down, but not sure how.

I suppose I need to isolate as follows

Jan 2010

Feb 2010

Mar 2010

Jan 2011

Feb 2011

Mar 2011

2.) The other issue I need to work out is Location on [ Question: Use QlikView to compare year over year on line chart ] is identical to SITE on the above spreadsheet. How would I link the 2?

Location = Rack 1 is the same as Site = Rack 1.

Reading through various Qlikview documents, it seems to be 'proper' to have them linked since they are the same thing.

6 Replies
kevincagle
Contributor III
Contributor III
Author

Can anyone provide assistance?

shiveshsingh
Master
Master

I couldn't understand your first query.

Regarding the second one, why don't you change the column name while loading? What is the purpose of linking? Are you creating a data model?

kevincagle
Contributor III
Contributor III
Author

How do I change a name of a column on load? I tried to rename the column to see if it would 'combine' both but couldn't figure out what I was doing wrong. I know I had the wrong syntax and what I was reading in the forums and help docs kept referring to concatenation as the method of combining.

Is this correct?

Data:

LOAD [SiteAddress] as Location,

           Row, 

           Headcount

FROM

[...Filename.xlsx]

(ooxml, embedded labels, table is [SheetName]);

For question 1, I need to translate this information to a graph (line/trellis/ or other chart) that shows 1 year over the other by the month.

Anil_Babu_Samineni

Instead of reading entire article, I suggest you to share mockup application

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)
kevincagle
Contributor III
Contributor III
Author

Here is an example file.

Unfortunately Excel wouldn't cooperate so I had to paste the sample below. I extend my apologies. BBM code doesn't appear to work here.

Copy and paste into a XLSX file located in the same folder as the Qlikview file and name it "mockup.xlsx".

   

SiteAddressCityStateYearMonthHeadcount
123 Roman AveHornAK2010Jan6
123 Roman AveHornAK2010Feb8
123 Roman AveHornAK2010Mar9
123 Roman AveHornAK2010Apr4
123 Roman AveHornAK2010May7
123 Roman AveHornAK2010Jun11
123 Roman AveHornAK2010Jul16
123 Roman AveHornAK2010Aug16
123 Roman AveHornAK2010Sep14
123 Roman AveHornAK2010Oct17
123 Roman AveHornAK2010Nov18
123 Roman AveHornAK2010Dec17
677 Home DrCupidCO2010Jan5
677 Home DrCupidCO2010Feb7
677 Home DrCupidCO2010Mar7
677 Home DrCupidCO2010Apr7
677 Home DrCupidCO2010May10
677 Home DrCupidCO2010Jun18
677 Home DrCupidCO2010Jul26
677 Home DrCupidCO2010Aug28
677 Home DrCupidCO2010Sep30
677 Home DrCupidCO2010Oct19
677 Home DrCupidCO2010Nov21
677 Home DrCupidCO2010Dec21
18098 Call ParkMoleneIL2010Jan5
18098 Call ParkMoleneIL2010Feb8
18098 Call ParkMoleneIL2010Mar6
18098 Call ParkMoleneIL2010Apr5
18098 Call ParkMoleneIL2010May7
18098 Call ParkMoleneIL2010Jun8
18098 Call ParkMoleneIL2010Jul8
18098 Call ParkMoleneIL2010Aug8
18098 Call ParkMoleneIL2010Sep7
18098 Call ParkMoleneIL2010Oct6
18098 Call ParkMoleneIL2010Nov8
18098 Call ParkMoleneIL2010Dec9
72 Awk CtBismarkND2010Jan11
72 Awk CtBismarkND2010Feb12
72 Awk CtBismarkND2010Mar16
72 Awk CtBismarkND2010Apr15
72 Awk CtBismarkND2010May16
72 Awk CtBismarkND2010Jun18
72 Awk CtBismarkND2010Jul19
72 Awk CtBismarkND2010Aug21
72 Awk CtBismarkND2010Sep21
72 Awk CtBismarkND2010Oct26
72 Awk CtBismarkND2010Nov27
72 Awk CtBismarkND2010Dec27
8827 Card RowAmarilloTX2010Jan6
8827 Card RowAmarilloTX2010Feb6
8827 Card RowAmarilloTX2010Mar9
8827 Card RowAmarilloTX2010Apr8
8827 Card RowAmarilloTX2010May8
8827 Card RowAmarilloTX2010Jun13
8827 Card RowAmarilloTX2010Jul14
8827 Card RowAmarilloTX2010Aug22
8827 Card RowAmarilloTX2010Sep25
8827 Card RowAmarilloTX2010Oct31
8827 Card RowAmarilloTX2010Nov38
8827 Card RowAmarilloTX2010Dec41
987 West Key LoopPanamaFL2010Jan7
987 West Key LoopPanamaFL2010Feb12
987 West Key LoopPanamaFL2010Mar15
987 West Key LoopPanamaFL2010Apr19
987 West Key LoopPanamaFL2010May18
987 West Key LoopPanamaFL2010Jun20
987 West Key LoopPanamaFL2010Jul24
987 West Key LoopPanamaFL2010Aug22
987 West Key LoopPanamaFL2010Sep19
987 West Key LoopPanamaFL2010Oct21
987 West Key LoopPanamaFL2010Nov23
987 West Key LoopPanamaFL2010Dec24
123 Roman AveHornAK2011Jan18
123 Roman AveHornAK2011Feb15
123 Roman AveHornAK2011Mar19
123 Roman AveHornAK2011Apr22
123 Roman AveHornAK2011May22
123 Roman AveHornAK2011Jun27
123 Roman AveHornAK2011Jul25
123 Roman AveHornAK2011Aug26
123 Roman AveHornAK2011Sep29
123 Roman AveHornAK2011Oct30
123 Roman AveHornAK2011Nov29
123 Roman AveHornAK2011Dec29
677 Home DrCupidCO2011Jan27
677 Home DrCupidCO2011Feb28
677 Home DrCupidCO2011Mar28
677 Home DrCupidCO2011Apr26
677 Home DrCupidCO2011May25
677 Home DrCupidCO2011Jun27
677 Home DrCupidCO2011Jul30
677 Home DrCupidCO2011Aug33
677 Home DrCupidCO2011Sep34
677 Home DrCupidCO2011Oct37
677 Home DrCupidCO2011Nov36
677 Home DrCupidCO2011Dec38
18098 Call ParkMoleneIL2011Jan8
18098 Call ParkMoleneIL2011Feb8
18098 Call ParkMoleneIL2011Mar9
18098 Call ParkMoleneIL2011Apr10
18098 Call ParkMoleneIL2011May10
18098 Call ParkMoleneIL2011Jun9
18098 Call ParkMoleneIL2011Jul9
18098 Call ParkMoleneIL2011Aug8
18098 Call ParkMoleneIL2011Sep9
18098 Call ParkMoleneIL2011Oct9
18098 Call ParkMoleneIL2011Nov10
18098 Call ParkMoleneIL2011Dec9
72 Awk CtBismarkND2011Jan20
72 Awk CtBismarkND2011Feb21
72 Awk CtBismarkND2011Mar21
72 Awk CtBismarkND2011Apr20
72 Awk CtBismarkND2011May23
72 Awk CtBismarkND2011Jun25
72 Awk CtBismarkND2011Jul28
72 Awk CtBismarkND2011Aug30
72 Awk CtBismarkND2011Sep31
72 Awk CtBismarkND2011Oct33
72 Awk CtBismarkND2011Nov34
72 Awk CtBismarkND2011Dec35
8827 Card RowAmarilloTX2011Jan16
8827 Card RowAmarilloTX2011Feb11
8827 Card RowAmarilloTX2011Mar13
8827 Card RowAmarilloTX2011Apr15
8827 Card RowAmarilloTX2011May15
8827 Card RowAmarilloTX2011Jun16
8827 Card RowAmarilloTX2011Jul17
8827 Card RowAmarilloTX2011Aug14
8827 Card RowAmarilloTX2011Sep17
8827 Card RowAmarilloTX2011Oct17
8827 Card RowAmarilloTX2011Nov17
8827 Card RowAmarilloTX2011Dec17
987 West Key LoopPanamaFL2011Jan27
987 West Key LoopPanamaFL2011Feb29
987 West Key LoopPanamaFL2011Mar35
987 West Key LoopPanamaFL2011Apr46
987 West Key LoopPanamaFL2011May59
987 West Key LoopPanamaFL2011Jun57
987 West Key LoopPanamaFL2011Jul63
987 West Key LoopPanamaFL2011Aug68
987 West Key LoopPanamaFL2011Sep74
987 West Key LoopPanamaFL2011Oct78
987 West Key LoopPanamaFL2011Nov88
987 West Key LoopPanamaFL2011Dec116
kevincagle
Contributor III
Contributor III
Author

Added example file.