Fun with pivot tables

Fri, 23 Sep 2016 11:51:45 GMT

Hi, I've got a very specific formatting requirement for my chart. I have chosen Pivot table as I believe it gets me closest. Also this is for consumption in both qlikview and nprinting

Full disclosure: I do have 1 solution for this I'll describe below but it is a bit brute force and requires me to do do all of the work in the database and would double my data since I now have 2 charts that are slightly different in important ways (data wise. formatting is identical)

So let's discuss requirements.

1. Header row with a color border below both dimensions and expressions - currently cheating on this one and I'm solving this in a report by adding the boarder there. In qlik I don't think we have a boarder. We can get away with a little more flexibility in qlik at the moment.

2. I need a sub total row below and a group row above - current solution is I add an extra subgroup row called "Fake" in the database results. and I have a super small dimension that is white on white to get the subtotal. It works. My only issue is in my new second chart group 2 has no data so I get the "Group 2" row but no subgroup rows and no subtotal row. I want to Hide the "Group 2 row" in this scenario.

3. Alternating colors but group rows, subtotal rows and total rows are all white. I'm so close on this one. Current solution is to return a color for the row from the database. But in chart 2 subgroup 3 doesn't exist. this means 2 yellow rows would be next to each other. If I use rank on the dimension value to determine the even and odd rows almost everything works great. Rank 1 and 0 are white. Other rows are white for even and yellow for odd. Rank only seems to look at the first character of the string for ranking purposes so I get some odd results. Second I have had problems with thinks like rank in the dimension coloring.

Here is what chart 2 would look like

Colors, Names, and numbers are fake for dramatic effect. Making Group 2 not show up looks like just a aggr so that should be relatively simple. Getting the alternating color for only the subgroup rows is proving to be a giant pain. I would rather not return another dataset from the db just to have the formatting for this chart.

P.S. just noticed on my second chart I'm missing a bar above the total row. That is a mistake not a requirement. the first chart is correct.

Re: Fun with pivot tables

Mon, 26 Sep 2016 14:17:19 GMT

Please hold off on solutions here. I realized I have too much details and too many questions. I'll break it up into multiple small easy questions and post the final result here for anyone curious.

topic Re: Fun with pivot tables in QlikView

Fun with pivot tables

Re: Fun with pivot tables