Cannot figure out crosstables

[#1]

I cannot for the life of me figure out how to work with cross tables that are using year on the Y axis and month on the X axis with customer data figures inside the table like the following table.

Year	January	February
2000	209	115
2001	412	250
2002	305	526

The months obviously carry all the way to December, and I have more years included. I have 3 different files formatted this same exact way but each with a different service in each that I would like to compare against each other in different graphs.

It is to my understanding that to work with a cross table like what I have here, I need to unpivot my table so that my information is usable. So this is what I did and my tables now look like this.

Year	Month	Data
2000	January	209
2000	February	115
2000	March	305
2000	April	145

This allowed me to create an association between my 3 files with the Year and I was able to display a graph showing data of customer data per year(adds up all of the months and displays the data from year to year).

That is about where things stopped coming together so easily. The next thing that I wanted to do was to display the % of change from year to year, and I cannot for the life of me figure out how to do this with the data I have given the program.

Obviously I would like to do a lot more than just display those two graphs with this software, so I am wondering if maybe there is something I am fundamentally doing wrong right off the bat with the way my tables are or maybe I could be handling data like this in a different way? I cannot find any resources involving using Qlik with a table like this, but this is the data that was provided to me. Could anyone point me towards some sort of resource that may help me figure out where to get started with data of this sort?

[Sergey_Shuklin]

Hello, Josh!

I've read your post several times and I still don't get what is the problem =/

You have a couple of tables that have some comlicate data interpretation or what?

Can you please share some instances of what you have for now and what you want to reach?

[#3]

The problem is that I have this data, but I cannot prepare it in charts visually as the client needs; yearly % change as an example. I was just showing truncated versions of the tables in the initial post. I have from 1981(I started here because other tables do have 1981 data) to 2017 in column1 and in the first row starting on column2, I have months listed off from January to December. I cannot find a simple way to show a % change per year, and this is only one of many metrics that are being displayed quite simply in excel that seems overly complicated on Qlik.

[Siva_Sankar]

Not clear to me though..may be you come up with a small example application to explain clearly...

what you want to achieve?

what is your current challenge?

-Siva

[Peter_Cammaert · Accepted solution]

Having problems in your Straight/Pivot table with not being able to compare a year to a previous year? Or a MonthYear to the same month in the previous year?

If your period is included as a dimension value, use Above() to get to the value of the previous "period".

Or see here for a few examples of comparisons of comparable periods in the current year vs the previous year:  Year-over-Year Comparisons

[Sergey_Shuklin]

For this table you can use this in the script:

CrossTable(month, val)

t1:

Load

year,

jan,feb,mar,apr and so on...

from path;

After that you'll get propriate data for creating a straight table where you'll be able to use "year" as dimension, sum(val) as value per year, and above() function to calculate the difference between years.

PFA.

[ahaahaaha]

Hi,

look here

Crosstable ‒ QlikView ‌

If briefly

Your source table

Script code

CrossTable (Month, Value)

LOAD * FROM [Your source table] ;

Result (look attached file)

With respect to calculate % of change from year to year and other questions. It would be nice to see a fragment of your data and the result that you want to get.

Regards,

Andrey

[Peter_Cammaert]

I think your problem is not with the CROSSTABLE LOAD. You seem to have serialized your data in a perfect way. The problem appears to be situated in how to best visualise Year-to-year comparisons.

To make any examples more fitting, care to post an example data source in xlsx or csv format? It's awfully difficult for QlikView to parse an image of a pivot table...

[#9]

I'm not having an issue with loading my data at all. I have all 3 of my tables loaded, and associated their years across the 3. I'm even able to create a line graph showing the values from each of the tables properly.

Its the more complicated visualizations I'm struggling to figure out how to display like % change from year to year. I've come to realize with another comment from Peter Cammaert in my thread is that what I want may only be able to be achieved by scripting into the expression field on my measure when trying to achieve. I'm definitely not against scripting, I'm just trying to learn the software and see what can be achieved in the GUI, and what things need to be scripted.

However, if you can provide more insight with access to my table, I will attach it as well in my post.

[#10]

I was able to solve my year to year % change with this formula. Your post led me in the right direction.

(Sum( {$<YEAR>} [TOTAL] ) - above(Sum( {$<YEAR>} [TOTAL] ))) / (Sum( {$<YEAR>} [TOTAL] ) + above(Sum( {$<YEAR>} [TOTAL] )) / 2) * 100