Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Cannot figure out crosstables

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.

YearJanuaryFebruary
2000209115

2001

412250
2002305526

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.

YearMonthData
2000

January

209
2000February115
2000March305
2000April145

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?

1 Solution

Accepted Solutions
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

View solution in original post

9 Replies
Sergey_Shuklin
Specialist
Specialist

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?

Not applicable
Author

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.table.png

Siva_Sankar
Master II
Master II

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
Partner - Champion III
Partner - Champion III

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
Specialist
Specialist

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
Partner - Master
Partner - Master

Hi,

look here

Crosstable ‒ QlikView

If briefly

Your source table

1.jpg

Script code

CrossTable (Month, Value)

LOAD * FROM [Your source table] ;


Result (look attached file)

2.jpg

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
Partner - Champion III
Partner - Champion III

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

Not applicable
Author

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.

line-graph.png

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.

Not applicable
Author

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