topic Pivot Table Help Required in QlikView

Pivot Table Help Required

cupelix-qlik — Mon, 25 May 2015 15:00:34 GMT

Is it possible within Qlikview a create a Cross-Tab data table which is colour coded relative to the other data. As per Image below from Excel.

I am still learning what can and can't be done in Qlikview. Now I know i can create a Pivot table to create the data, but I don't know how to colour it based on the selections, my Max & Min numbers are variable depending on which hospital I am looking at.

0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23
Monday	6	81	10	51	14	85	24	39	1	92	7	4	99	89	73	77	80	78	21	54	9	37	80	87
Tuesday	4	38	60	2	16	8	36	82	33	25	91	68	28	2	100	79	4	19	84	22	61	98	11	28
Wednesday	25	81	33	76	98	56	39	80	90	43	100	49	29	26	28	70	86	10	86	2	47	89	48	89
Thursday	47	73	24	38	95	83	4	11	11	34	25	30	65	21	30	30	45	52	26	35	48	87	8	65
Friday	21	49	30	6	43	99	98	70	83	69	94	66	38	20	70	40	11	67	13	8	68	54	91	20
Saturday	15	10	76	98	90	26	13	18	21	51	41	88	48	73	70	80	32	25	71	63	78	44	57	27
Sunday	67	94	35	88	46	76	28	88	58	44	33	84	93	17	21	5	11	41	75	9	57	22	42	69

Re: Pivot Table Help Required

swuehl — Tue, 02 Jun 2015 16:16:58 GMT

You can set the colors as expression color attribute in expression tab, open the expression attributes by clicking on the small plus sign next to the expression name on upper left.

You would need to create a color expression, something that returns a color code, for example a static color:

=RGB(100, 140, 50)

=lightgreen()

Or a dynamic color

=if( sum(Value) <= 10, lightred(), lightgreen() )

You would need to describe your requirements a bit more detailed (best by also uploading a small sample QVW), if you want more help on the expression.

Re: Pivot Table Help Required

cupelix-qlik — Thu, 09 Jul 2015 15:21:16 GMT

The data shows a Hourly Breakdown by Day for activity within Hospitals.

Each Hospital has a unique & variable pattern depending on the time scale selected (Week,Month, Quarter etc)

So in my example data above On the Wednesday @ 11 am there was 100 arrivals at the hospital within that Hour slot which would be coloured Green (for Max number anywhere within the data shown) where as Monday @ 9am there was only 1 arrival in that Hour Slot which would be Coloured Red (for Min Number within the data shown)

The Min can be a Zero and the Max could be any number. The Colour of each Hour Slot across the entire table is based on the Min & Max of the data shown. Red for Min & Green for Max this is then shaded on a sliding scale between those colours so that a mid range entry would appear Yellow/Orange.

As I can't know in advance what the Max or Min will be as this will determined by which Hospital has been selected and what time range has been selected.

So based on your Dynamic colour I wouldn't know what the value the 10 should be and that would only colour 2 colours rather than my example which has a sliding scale of colours to help identify where the problem area's are.

Hope this helps. I created the above in Excel so don't have a Qlikview doc to give.