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

Creation of line chart using cross tables

Hi

I need some help finishing a line chart.

I have 2 data loads that effectively contain the same data

Costing Table COSP:

P01OpeningPlanCostsGCV0,

P01OpeningPlanCostsGCV1,

P01PCGCV0,   

P02PCGCV0,

P03PCGCV0,

P04PCGCV0,

P05PCGCV0,

P06PCGCV0,

P07PCGCV0,

P08PCGCV0,

P09PCGCV0,

P10PCGCV0,

P11PCGCV0,

P12PCGCV0

P01PCGCV1,

P02PCGCV1,

P03PCGCV1,

P04PCGCV1,

P05PCGCV1,

P06PCGCV1,

P07PCGCV1,

P08PCGCV1,

P09PCGCV1,

P10PCGCV1,

P11PCGCV1,

P12PCGCV1

Costing Table COSS:

P01OpeningPlanCostsGCV0,

P01OpeningPlanCostsGCV1,

P01PCGCV0,   

P02PCGCV0,

P03PCGCV0,

P04PCGCV0,

P05PCGCV0,

P06PCGCV0,

P07PCGCV0,

P08PCGCV0,

P09PCGCV0,

P10PCGCV0,

P11PCGCV0,

P12PCGCV0

P01PCGCV1,

P02PCGCV1,

P03PCGCV1,

P04PCGCV1,

P05PCGCV1,

P06PCGCV1,

P07PCGCV1,

P08PCGCV1,

P09PCGCV1,

P10PCGCV1,

P11PCGCV1,

P12PCGCV1

Each field contains a value for a period. I need to calculate the difference between the fields and calculate a variance % which will be dsplayed in the line chart,  I also need to accumulate the values for each period. At he moment I'm trying to use the cross table function so that I can get the periods along the bottom of the chart and the values on the line.

Example

(P1PCGCV0 - P1GCV1) / P1PCGCV0) = Var %

The add P1 and P2 for the P2 point on the chart

I've attached a sample QVD wit the data in. I'm really struggling to get this done any help would be appreciated

18 Replies
marcus_sommer

I think you should transform your table with The Crosstable Load

- Marcus

Not applicable
Author

I have used the crosstable load. Its the next bit I'm stuck on

How to create the variance calc from the 2 fields and accumulate the values in the line chart

marcus_sommer

If you load those tables with an approach like this one: Creation of Line chart unless you used now value1 and value2 instead of value and you could in a following load join both tables and then you havbe both values within one table-row and could calculate with them.

- Marcus

sasikanth
Master
Master

HI,

try once

Create two flags

Load * ,'P1' as Flag FROM GCVO;

concatinate

LOAD *, 'P2' as Flag FROM COSS;

Crate a line chart with exp:

(sum({<Flag={'P1'}>} value)-sum({Flag={'P2'}>}value)) / Sum({<Flag={'P1'}>}value)

for accumulation you can select the same option that is available in Expression tab

or else

You can use RangeSum() function for the same

Not applicable
Author

Any chance you could demo in the script for me

Do you mean the COSP load  and COSS load should both use value or do you been they should be value1 and value 2

marcus_sommer

Try something like this:

t1:

CrossTable(Periode, Value1)

LOAD

     'Dummy' as Dummy,

P01OpeningPlanCostsGCV0,

P01OpeningPlanCostsGCV1,

P01PCGCV0,   

P02PCGCV0,

P03PCGCV0,

P04PCGCV0,

P05PCGCV0,

P06PCGCV0,

P07PCGCV0,

P08PCGCV0,

P09PCGCV0,

P10PCGCV0,

P11PCGCV0,

P12PCGCV0

P01PCGCV1,

P02PCGCV1,

P03PCGCV1,

P04PCGCV1,

P05PCGCV1,

P06PCGCV1,

P07PCGCV1,

P08PCGCV1,

P09PCGCV1,

P10PCGCV1,

P11PCGCV1,

P12PCGCV1

FROM [Costing Table COSP];

t2:

CrossTable(Periode, Value2)

LOAD

     'Dummy' as Dummy,

P01OpeningPlanCostsGCV0,

P01OpeningPlanCostsGCV1,

P01PCGCV0,   

P02PCGCV0,

P03PCGCV0,

P04PCGCV0,

P05PCGCV0,

P06PCGCV0,

P07PCGCV0,

P08PCGCV0,

P09PCGCV0,

P10PCGCV0,

P11PCGCV0,

P12PCGCV0

P01PCGCV1,

P02PCGCV1,

P03PCGCV1,

P04PCGCV1,

P05PCGCV1,

P06PCGCV1,

P07PCGCV1,

P08PCGCV1,

P09PCGCV1,

P10PCGCV1,

P11PCGCV1,

P12PCGCV1

FROM [Costing Table COSS];

t3:

Load Periode, Value1 Resident t1;

     join

Load Periode, Value2 Resident t2;

t4:

Load *, Value1 - Value2 as ValueDiff Resident t3;

drop tables t1, t2, t3;

- Marcus

Not applicable
Author

Hi

Almost there...but it doesn't like the Join in T3 ? The load just stops...any other ideas ?

marcus_sommer

Try:

t3:

Noconcatenate Load Periode, Value1 Resident t1;

     join

Load Periode, Value2 Resident t2;

- Marcus

Not applicable
Author

Hi

No it still just hangs and won't load