Pivot Chart - Configuring dynamic variance with formatting

amit_shetty78 — Fri, 23 Sep 2016 13:00:22 GMT

I have a requirement where I need to do variance analysis on stages of data received.

Dimension1	Dimension2	Stage	Value
D1	D2	S1	4320.6
D1	D2	S2	1680.41
D1	D2	S3	2386.55
D1	D2	S4	2828.67
D1	D2	S5	2828.67
D1	D3	S1	1643.43
D1	D3	S2	1643.43
D1	D3	S3	1700
D1	D3	S4	1700
D1	D3	S5	1700

By default there would be just 5 stages S1, S2, S3, S4 and S5.

So my resulting pivot chart would be:

Dimension1	Dimension2	S1	S2	S3	S4	S5
D1	D2	4320.6	1680.41	2386.55	2828.67	2828.67
D1	D3	1643.43	1643.43	1700	1700	1700

Here the values in S2 – S5 are color coded depending on it being greater than, less than or equal to the previous value. E.g., S2 in the first row if less that S1 in the same row, hence S2 value is red.

If there is an increase in the value then it is orange and if it’s the same then its green.

I am calculating the S1-S2 values using set analysis, hence the stages are hard coded in the expressions:

Following is the expression I have for S2. Also have an expression on the Text color of the expression to show it as Red, Green or Orange.

=IF(Sum({$<Stage={'S1'}>}Value) > Sum({$<Stage={'S2'}>}Value)

,' ▼ '

,IF(Sum({$<Stage={'S1'}>}Value) < Sum({$<Stage={'S2'}>}Value)

,' ▲ ',' '

)

) & ' ' & Sum({$<Stage={'S2'}>}Value)

Here is my difficulty, there is a possibility that sometime there may be an intermediate stage say S2-1 which should appear between S2 and S3 and variance calculated. I would like to make it dynamic and also show the conditional formatting of the values.

What is the best way to implement this? I can use a normal pivot with the Stage as a dimension and then transpose the dimension to show as columns. But doing this how do I also apply the formatting?

Appreciate your help on this.

Re: Pivot Chart - Configuring dynamic variance with formatting

sunny_talwar — Fri, 23 Sep 2016 13:08:04 GMT

Would you be able to share a sample of your intermediate application with the extra bit of difficulty you are having?

Re: Pivot Chart - Configuring dynamic variance with formatting

antoniotiman — Fri, 23 Sep 2016 13:20:30 GMT

Hi Amit,

see attachment.

Regards,

Antonio

Re: Pivot Chart - Configuring dynamic variance with formatting

sunny_talwar — Fri, 23 Sep 2016 13:24:39 GMT

Hey antoniotiman‌ -

Did you read this section in the OP?

Did not see anything that would address this? or am I missing something?

Best,

Sunny

Re: Pivot Chart - Configuring dynamic variance with formatting

antoniotiman — Fri, 23 Sep 2016 13:31:52 GMT

Hi Sunny,

I need new sample data.

Re: Pivot Chart - Configuring dynamic variance with formatting

amit_shetty78 — Fri, 23 Sep 2016 14:03:33 GMT

Thanks Antonio Mancini.

This is exactly what I was looking for.

topic Pivot Chart - Configuring dynamic variance with formatting in QlikView

Pivot Chart - Configuring dynamic variance with formatting

Re: Pivot Chart - Configuring dynamic variance with formatting

Re: Pivot Chart - Configuring dynamic variance with formatting

Re: Pivot Chart - Configuring dynamic variance with formatting

Re: Pivot Chart - Configuring dynamic variance with formatting

Re: Pivot Chart - Configuring dynamic variance with formatting