Change rate - Couple of questions

[kevincagle]

My Level of Qlikview = Beginner

I have data I need to display on a line graph to show changes over time.

I have many locations throughout the world and will incorporate later on.

Month is spelled out (January) or abbreviated (Jan) intermixed on the spread sheet.

I have a few questions and so far the topics that cover it are not spelling out how to do it, rather they are providing downloadable Qlikview files. I prefer to avoid downloading another persons file.

My test files are attached. I think I am properly showing the Monthly headcount compared to 1 year prior.

Question 1.) How do I make Qlikview automatically change month name January to Jan, state name Massachusetts to MA, day name Monday to Mon? This answer will probably correct the display issue that leads to question 2.

Question 2.) With no selections made, does the graph show total for all locations for the set dimensions?

[Frank_Hartmann]

Maybe like attached.

You can use Mapping Load/Applymap in script to achieve this.

hope this helps

[kevincagle]

Can you provide the steps?

[Frank_Hartmann · Accepted solution]

try this in script and reload:

Map1:
Mapping Load * Inline [
MonthLong, Month
January, Jan
February, Feb
March, Mar
April, Apr
May, May
June, Jun
July, Jul
August, Aug
September, Sep
October, Oct
November, Nov
December, Dec
];

Map2:
Mapping Load * Inline [
StateLong, State
Massachusetts, MA
];

LOAD City, 
     Headcount, 
     ApplyMap('Map1',Month) as Month,
     SiteAddress, 
     ApplyMap('Map2',State) as State,
     Year
FROM
[mockup.xlsx]
(ooxml, embedded labels, table is EmpCount);

[kevincagle]

Oh, OK. I see how it ties in now. Let me give that a go. Thank you Frank!

And question number 2

With no selections made, does the graph show total for all locations for the month and year of the set dimensions?

[Frank_Hartmann]

Yes!

[jobsonkjoseph]

Hi Frank,

What would be the expression used here.

[kevincagle]

The expression I used is this

Sum({<Year = {'2010'}>}Headcount)

I applied 2 expressions.

Year 2010 and one for 2011.

On the SORT tab, select EXPRESSION and place the following code. Set the STATE checkbox to ASCENDING. This will order the months in linear format.

Date#(Only({1} Month), 'MMMM')

[jobsonkjoseph]

Thanks for the update Kevin.

[kevincagle]

Ok, I have figured out how to map and such, now on to the next issue.

This question lines up with this topic so Ill try to avoid starting an unnecessary thread for it.

Question: Address format from (a) spreadsheet 1 and (b) spreadsheet 2.

(a) 123 Abigail Ave

(b) 123 Abigail Avenue, Gene, MA 12345

When mapping, how does it work with the comma's in the entry?

AddressList:
Mapping Load * Inline [
LocationLong, Location
123 Abigail Avenue, Gene, MA 12345, 123 Abigail Ave

];

It maps the address to Gene, but I want it to map to the abbreviated 123 Abigail Ave.