topic Re: change over time in QlikView

change over time

kevincagle — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: change over time

kevincagle — Thu, 10 May 2018 14:46:14 GMT

Can anyone provide assistance?

Re: change over time

shiveshsingh — Thu, 10 May 2018 15:35:36 GMT

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?

Re: change over time

kevincagle — Thu, 10 May 2018 17:13:46 GMT

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.

Re: change over time

Anil_Babu_Samineni — Fri, 11 May 2018 02:04:20 GMT

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

Re: change over time

kevincagle — Fri, 11 May 2018 16:58:40 GMT

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

Re: change over time

kevincagle — Thu, 24 May 2018 14:18:46 GMT

Added example file.