Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: Creation of line chart using cross tables

I think you should transform your table with The Crosstable Load

- Marcus

Not applicable

Re: Creation of line chart using cross tables

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

Re: Creation of line chart using cross tables

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
Valued Contributor III

Re: Creation of line chart using cross tables

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

Re: Creation of line chart using cross tables

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

Re: Creation of line chart using cross tables

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

Re: Creation of line chart using cross tables

Hi

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

Re: Creation of line chart using cross tables

Try:

t3:

Noconcatenate Load Periode, Value1 Resident t1;

     join

Load Periode, Value2 Resident t2;

- Marcus

Not applicable

Re: Creation of line chart using cross tables

Hi

No it still just hangs and won't load

Community Browser