# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for
Did you mean:
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
Contributor III
Author

Can anyone provide assistance?

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?

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:

Row,

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

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

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