Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Site | Year | Personnel | City | State | Month |
---|---|---|---|---|---|
Rack 1 | 2010 | 5 | St Louis | MO | Jan |
Rack 1 | 2010 | 6 | St Louis | MO | Feb |
Rack 1 | 2010 | 1 | St Louis | MO | Mar |
Rack 1 | 2011 | 8 | St Louis | MO | Jan |
Rack 1 | 2011 | 9 | St Louis | MO | Feb |
Rack 1 | 2011 | 6 | St Louis | MO | Mar |
Rack 2 | 2010 | 4 | St Louis | MO | Jan |
Rack 2 | 2010 | 7 | St Louis | MO | Feb |
Rack 2 | 2010 | 13 | St Louis | MO | Mar |
Rack 2 | 2011 | 14 | St Louis | MO | Jan |
Rack 2 | 2011 | 9 | St Louis | MO | Feb |
Rack 2 | 2011 | 17 | St Louis | MO | Mar |
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.
Can anyone provide assistance?
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?
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.
Instead of reading entire article, I suggest you to share mockup application
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".
SiteAddress | City | State | Year | Month | Headcount |
123 Roman Ave | Horn | AK | 2010 | Jan | 6 |
123 Roman Ave | Horn | AK | 2010 | Feb | 8 |
123 Roman Ave | Horn | AK | 2010 | Mar | 9 |
123 Roman Ave | Horn | AK | 2010 | Apr | 4 |
123 Roman Ave | Horn | AK | 2010 | May | 7 |
123 Roman Ave | Horn | AK | 2010 | Jun | 11 |
123 Roman Ave | Horn | AK | 2010 | Jul | 16 |
123 Roman Ave | Horn | AK | 2010 | Aug | 16 |
123 Roman Ave | Horn | AK | 2010 | Sep | 14 |
123 Roman Ave | Horn | AK | 2010 | Oct | 17 |
123 Roman Ave | Horn | AK | 2010 | Nov | 18 |
123 Roman Ave | Horn | AK | 2010 | Dec | 17 |
677 Home Dr | Cupid | CO | 2010 | Jan | 5 |
677 Home Dr | Cupid | CO | 2010 | Feb | 7 |
677 Home Dr | Cupid | CO | 2010 | Mar | 7 |
677 Home Dr | Cupid | CO | 2010 | Apr | 7 |
677 Home Dr | Cupid | CO | 2010 | May | 10 |
677 Home Dr | Cupid | CO | 2010 | Jun | 18 |
677 Home Dr | Cupid | CO | 2010 | Jul | 26 |
677 Home Dr | Cupid | CO | 2010 | Aug | 28 |
677 Home Dr | Cupid | CO | 2010 | Sep | 30 |
677 Home Dr | Cupid | CO | 2010 | Oct | 19 |
677 Home Dr | Cupid | CO | 2010 | Nov | 21 |
677 Home Dr | Cupid | CO | 2010 | Dec | 21 |
18098 Call Park | Molene | IL | 2010 | Jan | 5 |
18098 Call Park | Molene | IL | 2010 | Feb | 8 |
18098 Call Park | Molene | IL | 2010 | Mar | 6 |
18098 Call Park | Molene | IL | 2010 | Apr | 5 |
18098 Call Park | Molene | IL | 2010 | May | 7 |
18098 Call Park | Molene | IL | 2010 | Jun | 8 |
18098 Call Park | Molene | IL | 2010 | Jul | 8 |
18098 Call Park | Molene | IL | 2010 | Aug | 8 |
18098 Call Park | Molene | IL | 2010 | Sep | 7 |
18098 Call Park | Molene | IL | 2010 | Oct | 6 |
18098 Call Park | Molene | IL | 2010 | Nov | 8 |
18098 Call Park | Molene | IL | 2010 | Dec | 9 |
72 Awk Ct | Bismark | ND | 2010 | Jan | 11 |
72 Awk Ct | Bismark | ND | 2010 | Feb | 12 |
72 Awk Ct | Bismark | ND | 2010 | Mar | 16 |
72 Awk Ct | Bismark | ND | 2010 | Apr | 15 |
72 Awk Ct | Bismark | ND | 2010 | May | 16 |
72 Awk Ct | Bismark | ND | 2010 | Jun | 18 |
72 Awk Ct | Bismark | ND | 2010 | Jul | 19 |
72 Awk Ct | Bismark | ND | 2010 | Aug | 21 |
72 Awk Ct | Bismark | ND | 2010 | Sep | 21 |
72 Awk Ct | Bismark | ND | 2010 | Oct | 26 |
72 Awk Ct | Bismark | ND | 2010 | Nov | 27 |
72 Awk Ct | Bismark | ND | 2010 | Dec | 27 |
8827 Card Row | Amarillo | TX | 2010 | Jan | 6 |
8827 Card Row | Amarillo | TX | 2010 | Feb | 6 |
8827 Card Row | Amarillo | TX | 2010 | Mar | 9 |
8827 Card Row | Amarillo | TX | 2010 | Apr | 8 |
8827 Card Row | Amarillo | TX | 2010 | May | 8 |
8827 Card Row | Amarillo | TX | 2010 | Jun | 13 |
8827 Card Row | Amarillo | TX | 2010 | Jul | 14 |
8827 Card Row | Amarillo | TX | 2010 | Aug | 22 |
8827 Card Row | Amarillo | TX | 2010 | Sep | 25 |
8827 Card Row | Amarillo | TX | 2010 | Oct | 31 |
8827 Card Row | Amarillo | TX | 2010 | Nov | 38 |
8827 Card Row | Amarillo | TX | 2010 | Dec | 41 |
987 West Key Loop | Panama | FL | 2010 | Jan | 7 |
987 West Key Loop | Panama | FL | 2010 | Feb | 12 |
987 West Key Loop | Panama | FL | 2010 | Mar | 15 |
987 West Key Loop | Panama | FL | 2010 | Apr | 19 |
987 West Key Loop | Panama | FL | 2010 | May | 18 |
987 West Key Loop | Panama | FL | 2010 | Jun | 20 |
987 West Key Loop | Panama | FL | 2010 | Jul | 24 |
987 West Key Loop | Panama | FL | 2010 | Aug | 22 |
987 West Key Loop | Panama | FL | 2010 | Sep | 19 |
987 West Key Loop | Panama | FL | 2010 | Oct | 21 |
987 West Key Loop | Panama | FL | 2010 | Nov | 23 |
987 West Key Loop | Panama | FL | 2010 | Dec | 24 |
123 Roman Ave | Horn | AK | 2011 | Jan | 18 |
123 Roman Ave | Horn | AK | 2011 | Feb | 15 |
123 Roman Ave | Horn | AK | 2011 | Mar | 19 |
123 Roman Ave | Horn | AK | 2011 | Apr | 22 |
123 Roman Ave | Horn | AK | 2011 | May | 22 |
123 Roman Ave | Horn | AK | 2011 | Jun | 27 |
123 Roman Ave | Horn | AK | 2011 | Jul | 25 |
123 Roman Ave | Horn | AK | 2011 | Aug | 26 |
123 Roman Ave | Horn | AK | 2011 | Sep | 29 |
123 Roman Ave | Horn | AK | 2011 | Oct | 30 |
123 Roman Ave | Horn | AK | 2011 | Nov | 29 |
123 Roman Ave | Horn | AK | 2011 | Dec | 29 |
677 Home Dr | Cupid | CO | 2011 | Jan | 27 |
677 Home Dr | Cupid | CO | 2011 | Feb | 28 |
677 Home Dr | Cupid | CO | 2011 | Mar | 28 |
677 Home Dr | Cupid | CO | 2011 | Apr | 26 |
677 Home Dr | Cupid | CO | 2011 | May | 25 |
677 Home Dr | Cupid | CO | 2011 | Jun | 27 |
677 Home Dr | Cupid | CO | 2011 | Jul | 30 |
677 Home Dr | Cupid | CO | 2011 | Aug | 33 |
677 Home Dr | Cupid | CO | 2011 | Sep | 34 |
677 Home Dr | Cupid | CO | 2011 | Oct | 37 |
677 Home Dr | Cupid | CO | 2011 | Nov | 36 |
677 Home Dr | Cupid | CO | 2011 | Dec | 38 |
18098 Call Park | Molene | IL | 2011 | Jan | 8 |
18098 Call Park | Molene | IL | 2011 | Feb | 8 |
18098 Call Park | Molene | IL | 2011 | Mar | 9 |
18098 Call Park | Molene | IL | 2011 | Apr | 10 |
18098 Call Park | Molene | IL | 2011 | May | 10 |
18098 Call Park | Molene | IL | 2011 | Jun | 9 |
18098 Call Park | Molene | IL | 2011 | Jul | 9 |
18098 Call Park | Molene | IL | 2011 | Aug | 8 |
18098 Call Park | Molene | IL | 2011 | Sep | 9 |
18098 Call Park | Molene | IL | 2011 | Oct | 9 |
18098 Call Park | Molene | IL | 2011 | Nov | 10 |
18098 Call Park | Molene | IL | 2011 | Dec | 9 |
72 Awk Ct | Bismark | ND | 2011 | Jan | 20 |
72 Awk Ct | Bismark | ND | 2011 | Feb | 21 |
72 Awk Ct | Bismark | ND | 2011 | Mar | 21 |
72 Awk Ct | Bismark | ND | 2011 | Apr | 20 |
72 Awk Ct | Bismark | ND | 2011 | May | 23 |
72 Awk Ct | Bismark | ND | 2011 | Jun | 25 |
72 Awk Ct | Bismark | ND | 2011 | Jul | 28 |
72 Awk Ct | Bismark | ND | 2011 | Aug | 30 |
72 Awk Ct | Bismark | ND | 2011 | Sep | 31 |
72 Awk Ct | Bismark | ND | 2011 | Oct | 33 |
72 Awk Ct | Bismark | ND | 2011 | Nov | 34 |
72 Awk Ct | Bismark | ND | 2011 | Dec | 35 |
8827 Card Row | Amarillo | TX | 2011 | Jan | 16 |
8827 Card Row | Amarillo | TX | 2011 | Feb | 11 |
8827 Card Row | Amarillo | TX | 2011 | Mar | 13 |
8827 Card Row | Amarillo | TX | 2011 | Apr | 15 |
8827 Card Row | Amarillo | TX | 2011 | May | 15 |
8827 Card Row | Amarillo | TX | 2011 | Jun | 16 |
8827 Card Row | Amarillo | TX | 2011 | Jul | 17 |
8827 Card Row | Amarillo | TX | 2011 | Aug | 14 |
8827 Card Row | Amarillo | TX | 2011 | Sep | 17 |
8827 Card Row | Amarillo | TX | 2011 | Oct | 17 |
8827 Card Row | Amarillo | TX | 2011 | Nov | 17 |
8827 Card Row | Amarillo | TX | 2011 | Dec | 17 |
987 West Key Loop | Panama | FL | 2011 | Jan | 27 |
987 West Key Loop | Panama | FL | 2011 | Feb | 29 |
987 West Key Loop | Panama | FL | 2011 | Mar | 35 |
987 West Key Loop | Panama | FL | 2011 | Apr | 46 |
987 West Key Loop | Panama | FL | 2011 | May | 59 |
987 West Key Loop | Panama | FL | 2011 | Jun | 57 |
987 West Key Loop | Panama | FL | 2011 | Jul | 63 |
987 West Key Loop | Panama | FL | 2011 | Aug | 68 |
987 West Key Loop | Panama | FL | 2011 | Sep | 74 |
987 West Key Loop | Panama | FL | 2011 | Oct | 78 |
987 West Key Loop | Panama | FL | 2011 | Nov | 88 |
987 West Key Loop | Panama | FL | 2011 | Dec | 116 |
Added example file.